Shulou(Shulou.com)06/01 Report--

Today, the editor to share with you is the MySQL master-slave configuration, many people do not understand, today the editor in order to let you know more about MySQL, so give you a summary of the following content, let's look down. I'm sure you'll get something.

● was going to buy a cloud data to provide some security for my new project. Ali Yun, Tencent Cloud turned around, 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.

There are two raspberry pies in ● 's home to back up mysql directly to them. There are tutorials on the Internet. After searching several articles in segmentfault mysql, it is of course unsuccessful to do what they say. If it is successful, I will not write this article. Ps: their tutorials are all right.

● starts to configure the master and slave servers, just like the online tutorials, because I just read them.

1. Master setting (master)

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

Server-id=1 / / set the id of mysql to identify the log file of log-bin=/var/lib/mysql/mysql-bin / / log-bin. Master-slave backup uses this log record to achieve # binlog-do-db=mysql1 # the name of the database to be backed up. If you back up multiple databases, you can set this option repeatedly # binlog-ignore-db=mysql2 # the name of the database that does not need to be backed up. Repeat setting this option # log-slave-updates=1 # this parameter must be added when the slave library is used as 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 # skips the error and continues the copy operation (optional)

Add two accounts to the main mysql for synchronization

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 primary mysql

two。 From Settings (slave)

Modify the mysql configuration file my.conf, so that both slave nodes are configured in the same way.

Server-id=2#log-bin=/var/lib/mysql/mysql-bi / / you don't have to set log files from mysql

By adding command parameters from 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 / / launch mysql > show slave status\ G / / query status. Both Slave_IO_Running and Slave_IO_Running indicate success for yes

3. Problems in setup

Last_Errno: 1146

When I set up the slave, my master mysql already has a table. At that time, the binlog binary log that created the table was not recorded, and the slave mysql could not write data. At this time, we had to manually import the database file into the slave mysql; because the online tutorials are all newly installed master msql and slave mysql, and there is no problem with the existing data.

Last_Errno: 1062Error 'Duplicate entry' xxxxx' for key 'PRIMARY'' on query

Primary key conflict, this problem is more complicated, there should be a table setting is wrong, this problem later found a solution on Stack Overflow

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

The above is a brief introduction to the master-slave configuration of MySQL. Of course, the differences in the detailed use of the above have to be understood by everyone. If you want to know more, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.