MySQL master-slave configuration study notes

● I was planning to buy some cloud data to provide some security for my new project. After a round of searching between Alibaba Cloud and Tencent Cloud, I concluded that they are too expensive. Isn't it just that the data is backed up? In that case, I might as well make a backup myself.

● I have two Raspberry Pis at home, so I can just back up MySQL to them. There are tutorials online, so I’ll start right away. I searched several articles on segmentfault mysql and did what they said, but of course it was unsuccessful. If it had been successful, I would not have written this article. ps: Their tutorials are all correct.

● Now we start configuring the master and slave servers, just like the online tutorials, because that’s what I followed.

1. Master settings (master)

Modify the mysql configuration file, usually in /etc/mysql/my.conf

server-id=1 //Set mysql's ID identifierlog-bin=/var/lib/mysql/mysql-bin //log-bin log file, master-slave backup is implemented using this log record#binlog-do-db=mysql1 #Name of the database to be backed up. If you back up multiple databases, just set this option repeatedly#binlog-ignore-db=mysql2 #Name of the database that does not need to be backed up. If you back up multiple databases, just set this option repeatedly#log-slave-updates=1 #This parameter must be added when the slave library is also the master library of other slave libraries, otherwise the updated records will not be written to the binary file in binglog#slave-skip-errors=1 #Skip errors and continue the replication operation (optional)

Add 2 accounts for synchronization in the main mysql

mysql>grant replication slave on *.* to 'sync-1'@'%' identified by '123456';

mysql>grant replication slave on *.* to 'sync-2'@'%' identified by '123456';

Restart msql

mysql>show master status; //You can view the status of the master mysql

2. Slave settings

Modify the MySQL configuration file my.conf. The configuration methods of the two slave nodes are the same.

server-id=2
#log-bin=/var/lib/mysql/mysql-bi //No need to set log file from mysql

Add command parameters in mysql, master_log_file and master_log_pos can be queried in master mysql using show master status

mysql>change master to master_host='192.168.145.222',master_user='sync-1',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=308; 

mysql>start slave //Startmysql>show slave status\G //Query status, if both Slave_IO_Running and Slave_IO_Running are yes, it means success

3. Problems encountered during setup

Last_Errno: 1146

When I set up the slave, my master MySQL already had a table. There was no record in the binlog binary log when the table was created, so the slave MySQL could not write data. At this time, I had to manually import the database file into the slave MySQL. The reason was that the online tutorials all showed newly installed master MySQL and slave MySQL, so there was no problem of existing data.

Last_Errno: 1062

Error 'Duplicate entry 'xxxxx' for key 'PRIMARY'' on query

Primary key conflict. This problem is more complicated. It should be that the table settings are incorrect. I later found a solution to this problem on Stack Overflow.

# on slave
mysql> stop slave;
mysql> flush privileges;
# on master rest master
mysql> reset master;
# on slave;
mysql> reset slave;
mysql> start slave;

This is the end of this article about the study notes on MySQL master-slave configuration. For more detailed information on MySQL master-slave configuration, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!