MySql Master-Slave Replication Configuration
The Post Created(Updated) On 04/22/2022,Please note the timeliness of the article!
recently added a few servers for fun, now there are two hosts are having a database on the thought of getting a master-slave replication to serve as a backup of data, how important this data is without me saying it!
Master node configuration
Operation on the host
- The master adds the following to the database configuration file and turns on binlog
log-bin
server-id=1
- Restart the database
-
Export all tables of the master node and synchronize them to the child host
mysqldump -uroot -p password -A > /tmp/out.sql
scp out.sql root@child node host IP:/tmp/
Operations in the database
- Login to the database
mysql -uroot -p
- Create a sync account/slave node to guide this account with a connection
CREATE USER 'user'@'slave node IP address' IDENTIFIED BY 'password';
grant replication slave, replication client on *. * to 'user'@'slave IP address';
flush privileges;
````
- Check the status of the master node, remember the `File` and `Position`, suppose `File` is: emperinter.88888888, `Position` is: 888888888, the slave node database configuration should be used to start.
```sql
show master status;
- Database user change password
set password for username@from-node IP address = password('new password');
Slave node configuration
Operations on the host
- Importing the database
cd /tmp/
mysql -uroot -p < out.sql
- Configure, same as master, note that
server-id
and master should not be the same
server-id=2
Operations in the database
- Login to the database
mysql -uroot -p
- Configure the synchronization information, `Note to change the information for the master node! “
change master to master_host='master_node_IP',master_user='master_node_user',master_password='master_node_user_password',MASTER_LOG_FILE='emperinter.88888888',MASTER_LOG _POS=88888888;
- Start synchronization
start slave;
- Check the status of the slave node
show slave status\G
- Suspend synchronization
stop slave;
Copyright
Unless otherwise noted, all work on this blog is licensed under a Attribution-NonCommercial-NoDerivatives 4.0 International (CC BY-NC-ND 4.0) License. Reprinted with permission from -https://blog.emperinter.info/2022/04/22/mysql-master-slave-replication-configuration