Linux, UNIX

Creating MySQL replicas for scaling and high availability to reset slave settings: RESET SLAVE; (Use RESET SLAVE ALL; for MySQL 5.5.16 and later) When your  application is small, you can use a single  MySQL server for all your database needs. As your application becomes popular and you get more and more requests, the database starts becoming a bottleneck for application performance. With thousands of queries per second, the database write queue gets longer and read latency increases. To solve this problem, you can use multiple replicas of the same database and separate read and write queries between them. In this recipe, we will learn how to set up replication with the MySQL server. Getting ready You will need two MySQL servers and access to administrative accounts on both. Make sure that port  3306  is open and available on both servers. How to do it… Follow these steps to create MySQL replicas: Create the replication us...

Optimizing MySQL performance – configuration MySQL has  hundreds of settings that can be configured. Version  5.7  ships with many improvements in default configuration values and requires far fewer changes. In this recipe, we will look at some of the most important parameters for tuning MySQL performance. Getting ready You will need access to a root account or an account with  sudo  privileges. You will need access to a root account on the MySQL server. How to do it… Follow these steps to improve MySQL configuration: First, create a backup of the original configuration file: $ cd /etc/mysql/mysql.conf.d $ sudo cp mysqld.cnf mysqld.cnf.bkp Now open  my.cnf  for changes: $ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf Adjust the following settings for your InnoDB tables: innodb_buffer_pool_size = 512M # around 70% of total ram innodb_log_file_size = 64M innodb_file_per_table = 1 innodb_log_buffer_siz...