Mysql 5.6庫級表級複製的搭建
0. 架構資訊
主庫：192.168.56.100
從庫：192.168.56.200
1. 主庫操作
關閉資料庫
[root@localhost test]# mysqladmin -uroot -p shutdown
Enter password:
修改配置檔案
[root@localhost test]# vim /etc/my.cnf
[mysqld]
server-id=100
log-bin=/var/lib/mysql/mysqld-bin
binlog_format=row
# 複製的資料庫
binlog-do-db=test
啟動資料庫
[root@localhost test]# mysqld_safe --defaults-file=/etc/my.cnf &
可以透過下面命令檢視要複製的資料庫
[root@localhost test]# mysql -uroot -p
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysqld-bin.000003 | 120 | test | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
建立複製使用者
mysql> GRANT REPLICATION SLAVE ON *.* to 'repliform'@'%' identified by 'repliform';
Query OK, 0 rows affected (0.00 sec)
2. 從庫操作
關閉資料庫
[root@localhost tmp]# mysqladmin -uroot -p shutdown
Enter password:
增加複製引數到配置檔案中
[root@localhost tmp]# vim /etc/my.cnf
[mysqld]
server-id=200
log-bin=/var/lib/mysql/mysqld-bin
binlog_format=row
# 應用複製的表
replicate_do_table= test.emp
replicate_do_table= test.dept
配置複製
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.56.100',
-> MASTER_PORT=3306,
-> MASTER_USER='repliform',
-> MASTER_PASSWORD='repliform',
-> master_log_file='mysqld-bin.000001',
-> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.56.100
Master_User: repliform
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysqld-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table: dept.dept,dept.emp
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 120
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.100
Master_User: repliform
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000001
Read_Master_Log_Pos: 901
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 1065
Relay_Master_Log_File: mysqld-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table: test.dept,test.emp
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 901
Relay_Log_Space: 1242
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 04ebf096-10cf-11e6-8077-080027e76b2b
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
3. 測試同步效果
在主庫上面插入資料
mysql> insert into emp values (60), (70);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into dept values (60), (70);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
在從庫上面檢視同步效果
mysql> select * from test.emp;
+------+
| id |
+------+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
+------+
7 rows in set (0.00 sec)
mysql> select * from test.dept;
+--------+
| deptno |
+--------+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
+--------+
7 rows in set (0.00 sec)
主庫：192.168.56.100
從庫：192.168.56.200
1. 主庫操作
關閉資料庫
[root@localhost test]# mysqladmin -uroot -p shutdown
Enter password:
修改配置檔案
[root@localhost test]# vim /etc/my.cnf
[mysqld]
server-id=100
log-bin=/var/lib/mysql/mysqld-bin
binlog_format=row
# 複製的資料庫
binlog-do-db=test
啟動資料庫
[root@localhost test]# mysqld_safe --defaults-file=/etc/my.cnf &
可以透過下面命令檢視要複製的資料庫
[root@localhost test]# mysql -uroot -p
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysqld-bin.000003 | 120 | test | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
建立複製使用者
mysql> GRANT REPLICATION SLAVE ON *.* to 'repliform'@'%' identified by 'repliform';
Query OK, 0 rows affected (0.00 sec)
2. 從庫操作
關閉資料庫
[root@localhost tmp]# mysqladmin -uroot -p shutdown
Enter password:
增加複製引數到配置檔案中
[root@localhost tmp]# vim /etc/my.cnf
[mysqld]
server-id=200
log-bin=/var/lib/mysql/mysqld-bin
binlog_format=row
# 應用複製的表
replicate_do_table= test.emp
replicate_do_table= test.dept
配置複製
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.56.100',
-> MASTER_PORT=3306,
-> MASTER_USER='repliform',
-> MASTER_PASSWORD='repliform',
-> master_log_file='mysqld-bin.000001',
-> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.56.100
Master_User: repliform
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysqld-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table: dept.dept,dept.emp
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 120
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.100
Master_User: repliform
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000001
Read_Master_Log_Pos: 901
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 1065
Relay_Master_Log_File: mysqld-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table: test.dept,test.emp
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 901
Relay_Log_Space: 1242
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 04ebf096-10cf-11e6-8077-080027e76b2b
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
3. 測試同步效果
在主庫上面插入資料
mysql> insert into emp values (60), (70);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into dept values (60), (70);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
在從庫上面檢視同步效果
mysql> select * from test.emp;
+------+
| id |
+------+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
+------+
7 rows in set (0.00 sec)
mysql> select * from test.dept;
+--------+
| deptno |
+--------+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
+--------+
7 rows in set (0.00 sec)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-2142726/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 快速搭建streams表級複製環境
- MySQL：5.6 升級 5.7MySql
- Mysql分散式部署 - 多級複製MySql分散式
- MySQL級聯複製中資料同步MySql
- MySQL 5.5級聯複製配置流程MySql
- MySQL5.6複製原理圖MySql
- Mysql5.6主從複製MySql
- mysql5.6複製新特性MySql
- MySQL入門--MySQL複製技術之主從從級聯複製MySql
- MySQL級聯複製的同步問題（一）MySql
- 資料庫升級到mysql5.6遇到的資料表ENGINE問題資料庫MySql
- mysql5.1升級5.6MySql
- MySQL複習筆記(05)：MySQL表級鎖和行級鎖MySql筆記
- MySQL 5.6並行複製的架構圖MySql並行架構
- DM7資料複製之資料庫級複製資料庫
- ORACLE10g Stream表級複製配置Oracle
- MySQL複製--最快的從庫搭建方法（tar包）MySql
- MySQL表複製MySql
- 【資料庫】mysql5.6升級至5.7（物理方式）資料庫MySql
- MYSQL主從複製的搭建MySql
- MYSQL主主複製的搭建MySql
- mysql:sql as 複製表MySql
- mysql5.6.xGTID主從複製配置MySql
- mysql主從複製搭建MySql
- MySQL 5.5 複製搭建流程MySql
- mysql之 mysql 5.6不停機主從搭建（一主一從基於GTID複製）MySql
- centos7 mysql5.6升級5.7CentOSMySql
- Windows下Mysql5.6升級到5.7WindowsMySql
- 【Mysql】Mysql5.7的多源複製搭建MySql
- 【Mysql】傳統複製線上升級為GTID模式MySql模式
- mysql之 mysql 5.6不停機主從搭建（一主一從基於日誌點複製）MySql
- mysql之 mysql 5.6不停機主主搭建（活躍雙主基於日誌點複製）MySql
- MySQL級聯複製中的資料同步（第二篇）MySql
- Mysql5.6主從複製-基於binlogMySql
- MySQL複製資料表MySql
- MySQL 複製表結構MySql
- mysql表結構複製MySql
- MySQL-主從複製之搭建從資料庫MySql資料庫