mysql -u root -p < dump.sql
How to setup a single master with one or more readonly slaves
First configure the master by editing the mysql configuration file, this could be /etc/my.cnf or on Centos 7 it is /etc/my.cnf.d/server.cnf
Find the [server] section and add the following lines:
bind-address = 12.34.56.78 # replace 12.34.56.78 with the IP of your server log_bin server_id = 1 log_basename = master1 datadir = /var/lib/mysql binlog-ignore-db = mysql
The binlog-ignore-db line tells the master not to replicate the ‘mysql’ database, you can add additional entries to not replicate other databases if you wish, e.g.
binlog-ignore-db = information_schema binlog-ignore-db = performance_schema
Adding the following additional entries allows you to tweak the performance of your server:
max_allowed_packet=64M max_heap_table_size = 64M tmp_table_size = 128M join_buffer_size = 128M innodb_buffer_pool_size = 256M innodb_doublewrite = OFF innodb_additional_mem_pool_size = 128M innodb_flush_log_at_timeout = 4 innodb_read_io_threads = 48 innodb_write_io_threads = 32 max_connections = 128
Following any changes to the config files you will need to restart MySQL (or MariaDB if you use that instead), so you would do one of the following:
systemctl restart mysqld systemctl restart mariadb
Now login to your local MySQL server, e.g.
mysql -u root -p
Once logged into the MySQL CLI add a user that the slaves will use to replicate:
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'1.2.3.4' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
Replace 1.2.3.4 with the IP of your slave server. You can run the above command multiple times, once for each slave server. Remember to change ‘password’ to an actual password, a good choice would be 12 (or more) characters consisting of a random mix of upper/lowercase letters, numbers and punctuation characters.
Now we’re ready to take a backup of the master, for this you will need to open a second session on your master server. In the first session, still logged into the MySQL CLI issue the following commands:
FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
You should see something along these lines:
+--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | master1-bin.000001 | 456 | | mysql | +--------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
Leave this session in place whilst you move to the second session to backup the databases you want to replicate by using your preferred method, e.g.
mysqldump -u root -p --all-databases > dump.sql
Now you can go back to the first window and release the lock:
UNLOCK TABLES; QUIT;
Now copy this backup to your slave server(s). I use scp for this, e.g.
scp dump.sql username@12.34.56.78:.
Now login to the slave server and import the dumped databases:
mysql -u root -p < dump.sql
Now configure MySQL (or mariadb) to be a slave by editing it’s configuration file, e.g. /etc/my.cnf.d/server.cnf and add the following to the [server] section:
server-id = 2 datadir = /var/lib/mysql relay-log = /var/lib/mysql/mysql-relay-bin.log log_bin = /var/lib/mysql/mysql-bin.log
As per the master server, following any changes to the config files you will need to restart MySQL (or MariaDB if you use that instead), so you would do one of the following:
systemctl restart mysqld systemctl restart mariadb
The next step is to login to the MySQL CLI and tell it where to find the master along with the login details and the starting position in the log:
CHANGE MASTER TO MASTER_HOST='12.34.56.78',MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='master1-bin.000001', MASTER_LOG_POS=456;
The last thing we need to do is start the slave process, and check it is running. Do this by issuing the following commands on the slave MySQL CLI:
START SLAVE; SHOW SLAVE STATUS\G
You can repeat the slave part on each server you want to setup replication, just remember to give each slave a different server_id