Notes: 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.
1、Modify the configuration file my.cnf
1.1、Modify master’s my.cnf configuration file
1.1.1、This set enable binary log, master must, slave optional. Add, as follows:
log-bin=mysql-bin
1.1.2、This set master host mysql logo， it’s can be 1, can also take the host IP last paragraph. Add, as follows:
server-id=1
1.1.3、This set the database to push. Add, as follows:
binlog-do-db=ccupdate
1.1.4、This set Information to ignore. Add, as follows:
binlog-ignore-db=mysql
1.2、Modify slave’s my.cnf configuration file
1.2.1、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:
server-id=2
1.2.2、This set the database to update. Add, as follows:
replicate_wild_do_table=ccupdate.%
1.2.3、This set Information to ignore. Add, as follows:
replicate_wild_ignore_table=mysql.%
2、Create an authorization account and authorize on the master server
Execute, as follows:
grant replication slave on *.* to 'master'@'192.168.1.248' identified by '123456';
Tips：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.
3、Login to the master server’s mysql console and configure it
3.1、Refreshes all tables and prevents other writes. Execute, as follows:
flush tables with read lock;
3.2、View the master server from the state, record the table “File”, “Position” value. Execute, as follows:
show master status;
3.3、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.
3.4、Another opening of a shell. Execute, as follows:
mysqldump -pastercc --databases astercc10> astercc10.sql
3.5、Backup master astercc10 library, restore to the slave mysql. Execute, as follows:
mysql -pastercc < astercc10.sql
4、Login to the slave server’s mysql console and configure it
4.1、Let mysql slave get master of the replication permissions. Execute, as follows:
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's value(don't need " or ');
4.2、Start the mysql slave replication feature. Execute, as follows:
start slave;
4.3、Check the Master Slave Replication feature status from the mysql slave.
show slave status\G
4.4、The parameters “Slave_IO_Running: ” and “Slave_SQL_Running: ” are both “Yes”. Parameters Last_IO_Errno: 0, Last_IO_Error:, Last_SQL_Errno: 0, Last_SQL_Error:, no error message, the master-slave replication function has been achieved.
Tips: If the master with mysql5.6, binlog_checksum default is set to “crc32”, and slave with the 5.5 or earlier version, the master of the binglog_checksum set to none. You can execute set global binlog_checksum = ‘NONE’ on the console, or write my.cnf, and add binlog_checksum = NONE.