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】 ==== - Add server-id=2 it's can be 2, can also take the host IP last paragraph, the logo must be different from the master. - Add replicate_wild_do_table=ccupdate.% set the database to push. - Add replicate_wild_ignore_table=mysql.% set Information to ignore. ===== 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>解决grant replication slave ont 操作提示密码PASSWORD()，执行 show variables like 'old_passwords';若得到old_passwords = 1，则执行set old_passwords=0;解决该问题。 </note> ===== 三、登录master服务器的mysql控制台并配置。 ===== {{:zh:常见问题及解答:selection008.png?750|}} - 执行 flush tables with read lock; 刷新所有表并且阻止其它写入。 - 执行 show master status; 查看主服务器主从状态，记录表中File、Position下的值。 - 此后不要再操作主服务器mysql，防止主服务器状态值变化，勿退出mysql防止read unlock发生。 - 另开一个shell执行mysqldump -pastercc --databases astercc10 > astercc10.sql备份master的astercc10库并还原给slave。 ===== 四、登录slave服务器的mysql控制台并配置。 ===== - 执行 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>