MySQL master-Slave synchronization configuration steps

1. Enable the Log_bin log for the main library, set the log format binlog_format=mixed, or Binlog_format=row,
Enable the Server-id of the main library
#主库上加
Synchronization log #只记录 the Ka_app library
Binlog_do_db=ka_app

#不记录下列库的同步日志
Binlog_ignore_db=mysql
Binlog_ignore_db=performance_schema
Binlog_ignore_db=information_schema
Binlog_ignore_db=ka_tdf
Binlog_ignore_db=ka_model
Binlog_ignore_db=test

Restart the main library,/etc/init.d/mysql restart

2. Close the binary log_bin of the backup machine, comment, set Server-id, set the read-only property Read_only=on from the library,
Turn on relay log relay-log=mysql-relay from server, turn on relay index from server Relay-log-index=mysql-relay.index
#从库上加
#只同步 all table logs for Ka_app
replicate_wild_do_table=ka_app.%
#不同步下面库的所有表日志
Replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=performance_schema.%
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=ka_tdf.%
Replicate-wild-ignore-table=ka_model.%
replicate-wild-ignore-table=test.%

Restart from library/etc/init.d/mysql restart

3. Create a copy account on the main library (as needed)
Mysql>grant replication Slave on * * to ' repl ' @ ' 169.254.10.11 ' identified by ' repl ';
Mysql>flush privileges;

4. Import and Export data
Main Library Lock Export
Mysql>flush tables with read lock;
Mysqldump-uroot-pmysql--host=169.254.10.12--flush-logs--databases stock-app--master-data=1 > Backup-source.sql
Restoring from a library
mysql-uroot-p123456 < Backup-source.sql

5. Enable synchronization
Main Library View current log location
Mysql>show Master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000004 |              360 |                  | |
+------------------+----------+--------------+------------------+

Synchronize from the specified location from the library specified
Mysql>change Master to master_host= ' 169.254.10.12 ', master_port=3306,master_user= ' repl ', master_password= ' repl ', Master_log_file= ' mysql-bin.000004 ', master_log_pos=360;
Mysql>slave start;

6. Unlock the main library
Mysql>unlock tables;

7. View the status of Master-slave synchronization
Main Library
Mysql>show Master status;
From the Library
mysql>show slave status;

MySQL master-Slave synchronization configuration steps