Shulou(Shulou.com)05/31 Report--

How to configure master and slave in MySQL database? Many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can gain something.

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; is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.