MySQL AB复制 - 数据库编程

ates: 0 Warnings: 0Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.02 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> use larrydb;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_larrydb |
+-------------------+
| t2 |
| test |
+-------------------+
2 rows in set (0.01 sec)mysql> select * from t2;
+----+---------+
| id | name |
+----+---------+
| 1 | larry01 |
| 3 | larry02 |
| 4 | larry03 |
| 6 | larry04 |
| 7 | larry05 |
| 9 | larry07 |
+----+---------+
6 rows in set (0.01 sec)--serv08修改配置文件，打开log_slave_updates，重启MySQL服务
mysql> show variables like '%update%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| binlog_direct_non_transactional_updates | OFF |
| log_slave_updates | OFF |
| low_priority_updates | OFF |
| sql_low_priority_updates | OFF |
| sql_safe_updates | OFF |
+-----------------------------------------+-------+
5 rows in set (0.00 sec)[root@serv08 data]# vim /etc/my.cnf
[root@serv08 data]# cat /etc/my.cnf | grep log_slave_updates
log_slave_updates=1
[root@serv08 data]# /etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS!

第八步，serv01插入测试数据，我们看到打开这个参数后mysql-bin.000004和serv08-relay-bin.000011都有相关的插入数据的记录

mysql> insert into t2(name) values('larry08');
Query OK, 1 row affected (0.00 sec)mysql> select * from t2;
+----+---------+
| id | name |
+----+---------+
| 1 | larry01 |
| 3 | larry02 |
| 4 | larry03 |
| 6 | larry04 |
| 7 | larry05 |
| 9 | larry07 |
| 11 | larry08 |
+----+---------+
7 rows in set (0.00 sec)[root@serv08 data]# mysqlbinlog mysql-bin.000004 | grep larry
use `larrydb`/*!*/;
insert into t2(name) values('larry08')
[root@serv08 data]# mysqlbinlog serv08-relay-bin.000011 | grep larry
use `larrydb`/*!*/;
insert into t2(name) values('larry08')

第九步，serv08创建授权用户

mysql> select user,password,host from mysql.user where user='larry';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 3
Current database: larrydb+-------+-------------------------------------------+-------------+
| user | password | host |
+-------+-------------------------------------------+-------------+
| larry | *0CDC8D34246E22

MySQL AB复制(九)