This is an old revision of the document!

<note> This article describes how to set the master-slave replication from mysql, to synchronize the local and remote server mysql modify, delete data. Experiment has two local virtual machine master's IP address 192.168.1.80, set to push the contents of the mysql data to the slave server, slave host IP address is 192.168.1.248. </note> ===== Modify the configuration file my.cnf ===== ==== 【Modify master's my.cnf configuration file】 ==== *This set enable binary log, master must, slave optional. Add, as follows: <code>log-bin=mysql-bin</code> *This set master host mysql logo， it's can be 1, can also take the host IP last paragraph. Add, as follows: <code>server-id=1</code> *This set the database to push. Add, as follows: <code>binlog-do-db=ccupdate</code> *This set Information to ignore. Add, as follows: <code>binlog-ignore-db=mysql</code> ==== 【Modify slave's my.cnf configuration file】 ==== *This set slave host mysql logo, it's can be 2, can also take the host IP last paragraph, the logo must be different from the master. Add, as follows: <code>server-id=2</code> *This set the database to update. Add, as follows: <code>replicate_wild_do_table=ccupdate.%</code> *This set Information to ignore. Add, as follows: <code>replicate_wild_ignore_table=mysql.%</code> ===== Create an authorization account and authorize on the master server ===== *Execute, as follows: <code> grant replication slave on *.* to 'master'@'192.168.1.248' identified by '123456'; </code> <note tip>Solve the grant replication slave on operation prompt password PASSWORD () error, execute command "show variables like 'old_passwords';" If "old_passwords = 1", execute command "set old_passwords = 0;" solve the problem. </note> ===== Login to the master server's mysql console and configure it ===== {{:zh:常见问题及解答:selection008.png?750|}} *Refreshes all tables and prevents other writes. Execute, as follows: <code>flush tables with read lock;</code> *View the master server from the state, record the table "File", "Position" value. Execute, as follows: <code>show master status;</code> *Then do not operate the main server mysql, to prevent changes in the state of the main server, do not exit mysql prevent "read unlock" occurred. *Another opening of a shell. Execute, as follows: <code>mysqldump -pastercc --databases astercc10> astercc10.sql</code> *Backup master astercc10 library, restore to the slave mysql. Execute, as follows: <code>mysql -pastercc < astercc10.sql</code> ===== Login to the slave server's mysql console and configure it ===== - 执行 change master to master_host='192.168.1.80',master_user='ccmaster', master_password='passw0rd', master_log_file='mysql-bin.000001', master_log_pos=Position值(无引号); 使slave获得master的复制权限。 - 执行 start slave; 启动从服务器复制功能。 - 执行 show slave status\G 检查从服务器主从复制功能状态。 - 其中参数Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes 状态均为Yes。参数Last_IO_Errno: 0，Last_IO_Error:，Last_SQL_Errno: 0，Last_SQL_Error: ，无错误信息，则主从复制功能已经实现。 {{:zh:常见问题及解答:selection009.png?750|}} <note tip>如果 master 用的 mysql5.6 , binlog_checksum 默认设置的是 “crc32”，并且 slave 用的 5.5 或者更早的版本，请将 master 的 binglog_checksum 设置为 none。可以在控制台上执行 set global binlog_checksum='NONE'，或写入my.cnf，添加binlog_checksum=NONE永久生效。</note>