Mysql Cross server sync data experience sharing _mysql

Project needs to find some information and personal configuration after the experience to share.

(1) Primary server
Modify configuration file/etc/my.cnf (My.ini)
[Mysqld]
# Mysql-bin is the prefix of the log file, you can also use other names, such as server name
# If you don't have a path, you can write the log file to '/var/lib/mysql '
Log-bin=mysql-bin
# ServerID must be unique in a synchronization system, greater than or equal to 1 and less than 2^32-1 integers
Server-id=1

binlog-do-db = database name (the database you want to back up)
binlog-ignore-db = database name (you do not need to back up the database)
#以上两个都不选, back up all

#如果使用InnoDb, to ensure stability, you should also set the following two lines:
Innodb_flush_log_at_trx_commit=1
Sync_binlog=1
#并且确保没有设置skip-networking, disabling the network naturally fails to synchronize. But
#innodb_flush_log_at_trx_commit =1 can cause a sharp drop in the write data speed on some servers,
#可尝试调整为2.

#自己手动添加 and restart the MySQL server

[Optional action Add account]
#设置账户步骤:
#登录mysql主服务器,
[root@localhost ~]# mysql-uroot–p123456 login MySQL
#这里需要指明一下啊 This 123456 is my MySQL password
#添加一个叫做beifen的用户, and authorized to give from the server
mysql> grant replication Slave on *.* to ' Beifen ' @ ' 192.168.1.2 ' identified by ' 123 ';
#创建 beifen User, the password is set to 123 and authorized to 192.168.1.2 (IP is from the computer IP) use.

Mysql->show MASTER STATUS; #查看信息并记下File和Position的值,
#这个在配置从数据库的时候用的到

(2) from the server my.cnf settings (if you need two-way master, except Server-id, other configuration and the same as the primary server)
Server-id must be set, Binlog not open.
Modify configuration file/etc/my.cnf (My.ini)
Modify the service-id=1 to server-id=2
Restart the MySQL server and log on

[If there is data to process data first]
(someone says to perform mysql > stop slave when importing data; Not specifically tested)
Processing data methods:
FLUSH TABLES with READ LOCK; 1 Lock the primary server
2 Export data from the primary server,
3 Import from server
UNLOCK TABLES; 4 Unlock

#设置主从关系 (can also be used on the server, means bidirectional)
Mysql->change Master to master_host= ' 192.168.1.1 ', (primary server IP)
Master_user= ' Beifen ', master_password= ' 123 ',
Master_log_file= ' mysql-bin.000001 ', (primary server query out file value)
MASTER_LOG_POS=107 (primary server queries out position value)

Start slave sync
mysql > Start slave;

#检查主从同步, if you see slave_io_running and slave_sql_running are yes,
#则主从复制连接正常.
MySQL > show slave status\g