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;

! 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


CouPon

alibaba cloud20 dollars
Vultr10 Dollars
Bandwagon HostMaybe some Coupon?
Domain | namesiloemperinter 1Dollars