MySQL Master-slave configuration

I. Fundamentals of MySQL master-slave backup (replication)

MySQL supports one-way, asynchronous replication, during which one server acts as the primary server, and one or more other servers act as slave servers. MySQL replication tracks all changes to the database (updates, deletions, and so on) based on the primary server in the binary log. Therefore, to replicate, you must enable binary logging on the primary server. Each slave server receives a saved update from the primary server that the primary server has logged to the binary log. When a primary server is connected from the server, it notifies the primary server where the last successful update was read from the server in the log. Receives from the server any updates that have occurred since then, and performs the same updates on this computer. Then block and wait for the master server to notify the new updates. Performing a backup from the server does not interfere with the primary server, and the master server can continue to process updates during the backup process.
　　
two. mysql master-Slave backup configuration method
in MySQL master-slave backup, it is best to ensure that the master-slave server version is compatible. The server is at least the same or higher than the primary server version.
　　
1. Modify the Host configuration
Add the following lines under the [Mysqld] tab

log-bin                                       #开启二进制日志server-id=1                                 # Master server ID number, arbitrarily set, generally 1  #binlog-do-db=db_namea                        #指定对db_nameA记录二进制日志   # binlog-ignore-db=db_namb                      #指定不对db_namB记录二进制日志  general_log=on                              &nbsp, #开启sql语句记录, must not be general_log_file = d:/mysql-log-query.log    #指定保存位置, not necessary 

Attention:
Log-bin,server-id is the content that must be added in the configuration file. At this point, the primary server backs up all the databases by default. If you need to specifically indicate that a database is being backed up or not backed up, you can add the binlog-do-db and BINLOG-IGNORE-DB options. Restart the MySQL service after modification. Login Database Input:

Show global variables like '%log% ';

See if Gerneral_log and Long_bin are on, as well as the General_log_file storage location. Re-enter:

Show master status;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/7A/07/wKioL1ahfYmj6aq4AAAIHDq0G1E570.png "title=" i_2hz} zugt7u$9[) 72k9c_s.png "alt=" Wkiol1ahfymj6aq4aaaihdq0g1e570.png "/>

Remember the File and Position corresponding values that appear, configured from the opportunity.

2. To add a MySQL account from the server and configure permissions
On the primary server, you must create a user to connect to the primary server from the server, suggest creating a new user, and set replication slave permissions. The specific commands used are as follows:

Create user Forslave identified by ' Forslave ';      #用户名密码自己决定grant replication Slave on * * to ' forslave ' @ '% ' identified by ' forslave '; flush privileges;      #刷新权限select * from user where user= ' forslave ' \g; #查看权限

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/7A/09/wKioL1ahl53DuV_UAAAPJYJce-M515.png "title=" QQ picture 20160122104307.png "alt=" Wkiol1ahl53duv_uaaapjyjce-m515.png "/>

Note whether Repl_slava_priv is Y.

3. Slave machine (slave) configuration

1. Modify the MySQL configuration file my.cnf
Under the [Mysqld] tab, add the following line:

server-id=2 #从服务器id号, must be greater than the primary server ID set previously. General_log=on #开启sql语句记录, non-mandatory general_log_file = D:/mysql-log-query.log #指定保存位置, non-mandatory

Then restart the slave MySQL database, log in to the database with the root user and enter:

Change Master to Master_host = ' 10.52.10.75 ', #主机IP地址 master_user = ' Forslave ', #之前配置的用户及密码 MASTER_PA      ssWOrd = ' Forslave ', master_log_file = ' mysql-bin.000011 ', #master file counterpart in status Master_log_pos = 327; Position corresponding value in #master status

Check the database to see the relevant parameters, enter:

show slave status \g;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/7A/0D/wKioL1ahyzGT_lElAAAn8rayMVE267.png "title=" QQ picture 20160122112318.png "alt=" Wkiom1ahomojifftaaalh8xyuy4707.png "/>

Only part of the section is captured here, note that

slave_io_state:waiting for Master to send event
Slave_io_running:yes
Slave_sql_running:yes
The above parameters say listen to slave normal operation, is waiting for the main server to send messages.

The two parameters to note are:
Master_log_file and Read_master_log_pos. The master_log_file represents the log file name used for the primary and standby synchronization on the host, and Read_master_log_pos represents the location in the log files that were last successfully synced to.
If these two items do not match the values of file and position that were previously seen on the primary server, synchronization cannot be performed correctly.

4. Test Master-slave replication

At this point, the master-slave server configuration has been completed, the database can be operated on the host, the database from the computer will also have a corresponding change.

Also, you can view the SQL statements executed in General_log_file.

5. Note

Try to ensure that the data in the master-slave database is exactly the same before the master-slave setup. If it is not consistent, operating the data that is not on the machine on the host will appear in slave_status similar to the following error:

......

Slave_io_running:yes

Slave_sql_running:no

......

last_errno:1091

Last_error:error ' Can ' t drop database ' test '; Database doesn ' t exist ' on query. Default database: ' Test '. Query: ' Drop database Test ' #因所操作的内容而异

        

        last_sql_error:error ' Can ' t drop database ' #因所操作的内容而异

......

At this point you can do the following in the slave's database:

Stop slave;      #先停止服务set Global sql_slave_skip_counter=1;      #跳过1个错误event, if there are multiple errors can be 1 to modify the start slave;     #重新启动服务show slave status\g; #再次查看状态, return to normal, if there is a similar error, continue to perform the above operation

MySQL Master-slave configuration