Detailed explanation of MySQL master-slave configuration

● I planned to buy cloud data to provide some security for my new project. Alibaba Cloud and Tencent Cloud have gone around in a circle, two words are too expensive. Isn't there just a backup of the data? In that case, I might as well do the backup myself.

● I have two Raspberry Pis at home and just back up mysql directly to them. There are tutorials on the Internet, so I will start here. I searched several articles on segmentfault mysql and followed what they said. Of course, it was unsuccessful. If it had been successful, I would not have written this article. ps: Their tutorials are all correct.

● Let’s start configuring the master and slave servers, just like the online tutorials, because I just watched them.

1. Master settings (master)

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

server-id=1 //设置mysql的id标识
log-bin=/var/lib/mysql/mysql-bin  //log-bin的日志文件，主从备份就是用这个日志记录来实现的
#binlog-do-db=mysql1 #需要备份的数据库名，如果备份多个数据库，重复设置这个选项 即可
#binlog-ignore-db=mysql2 #不需要备份的数据库名，如果备份多个数据库，重复设置这 个选项即可
#log-slave-updates=1 #这个参数当从库又作为其他从库的主库时一定要加上，否则不会给更新的记录写到binglog里二进制文件里
#slave-skip-errors=1 #是跳过错误，继续执行复制操作(可选)

Add 2 in the main mysql for Synchronized account

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; //可以查看主mysql状态

2. Modify the mysql configuration file my.conf from settings (slave)

. The two slave nodes are configured in the same way.

server-id=2
#log-bin=/var/lib/mysql/mysql-bi //从mysql可以不用设置日志文件

After adding command parameters from mysql, master_log_file and master_log_pos can be queried using show master status in master mysql

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 //启动
mysql>show slave status\G //查询状态，Slave_IO_Running 和Slave_IO_Running都为yes表示成功

3. Problems in setting

Last_Errno: 1146

When I set up the slave, my master mysql already had a table. The binlog binary log of the created table had no record. The slave mysql could not write data. At this time, I had to manually import the database file into the slave mysql; The reason is that the online tutorials are all newly installed master msql and slave mysql, and there is no problem with 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 setting of the table is wrong. The solution to this problem was later found 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;

Recommended mysql video tutorial, address: https://www.php.cn/course/list/51.html

The above is the detailed content of Detailed explanation of MySQL master-slave configuration. For more information, please follow other related articles on the PHP Chinese website!