初识MySQL replication(异步复制)

今天在虚拟机上测试了下mysql的异步复制，选择的是mysql 5.5.37这个版本，下面为测试内容：
1.主库创建复制使用的账号，并授予REPLICATION SLAVE权限
创建用户orl5，可以从192.168.90.181的主机进行连接

(testing)root@localhost [(none)]> 
(testing)root@localhost [(none)]> GRANT REPLICATION SLAVE on *.* to 'orl5'@'192.168.90.181' identified by 'mysql12345';
Query OK, 0 rows affected (0.01 sec)
(testing)root@localhost [(none)]>

2.修改主数据库服务器的配置文件my.cnf，开启BINLOG，并设置server-id的值。
这两个参数的修改需要重新启动数据库才能生效

##设置前：
(testing)root@localhost [(none)]> 
(testing)root@localhost [(none)]> show global variables like '%bin%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_format                           | STATEMENT            |
| binlog_stmt_cache_size                  | 32768                |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| log_bin                                 | OFF                  |
| log_bin_trust_function_creators         | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| sql_log_bin                             | ON                   |
| sync_binlog                             | 0                    |
+-----------------------------------------+----------------------+
12 rows in set (0.00 sec)
(testing)root@localhost [(none)]> 
(testing)root@localhost [(none)]> 

##修改my.cnf文件，加入以下内容
log-bin = /data/mysql/mysql_3306/data/mysql-bin.log
server-id = 180

##重启mysql
[root@orl6 performance_schema]# 
[root@orl6 performance_schema]# service mysql restart
Shutting down MySQL.[  OK  ]
Starting MySQL..[  OK  ]
[root@orl6 performance_schema]# 
[root@orl6 performance_schema]# 

##参看参数是否生效
(testing)root@localhost [(none)]> 
(testing)root@localhost [(none)]> show global variables like '%bin%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_format                           | STATEMENT            |
| binlog_stmt_cache_size                  | 32768                |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| log_bin                                 | ON                   |
| log_bin_trust_function_creators         | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| sql_log_bin                             | ON                   |
| sync_binlog                             | 0                    |
+-----------------------------------------+----------------------+
12 rows in set (0.01 sec)
(testing)root@localhost [(none)]>

3.主库上设置读锁定有效，确保数据库没有操作，获得一致性的快照，
设置后数据库停止更新操作

(testing)root@localhost [(none)]> 
(testing)root@localhost [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
(testing)root@localhost [(none)]> 
(testing)root@localhost [(none)]>

4.查看得到主库上的当前二进制名称和偏移量值

(testing)root@localhost [(none)]> 
(testing)root@localhost [(none)]> show master status
    -> ;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
(testing)root@localhost [(none)]>

5.生成主数据库的备份
这里用tar包的方式直接备份

[root@orl6 mysql_3306]# 
[root@orl6 mysql_3306]# tar -cvf data.tar data
data/
data/orl6.pid
data/test/

6.恢复主库的写操作

(testing)root@localhost [(none)]> 
(testing)root@localhost [(none)]> unlock tables;
Query OK, 0 rows affected (0.00 sec)
(testing)root@localhost [(none)]>

7.拷贝tar包到从库存放数据文件的目录中解压

[root@orl5 mysql_3306]# 
[root@orl5 mysql_3306]# tar -xvf data.tar
data/
data/orl6.pid

8.修改从库的my.cnf文件，增加server-id参数，该参数必须是唯一的
在从库my.cnf文件中加入下面内容：

server-id = 181

9.启动从库，使用--skip-slave-start选项启动从数据库，
这样不会立即启动从数据库服务上的复制进程

[root@orl5 bin]# 
[root@orl5 bin]# ./mysqld_safe --skip-slave-start &
[1] 5183
[root@orl5 bin]# 140708 02:40:21 mysqld_safe Logging to '/data/mysql/mysql_3306/data/error.log'.
140708 02:40:21 mysqld_safe Starting mysqld daemon with databases from /data/mysql/mysql_3306/data
[root@orl5 bin]#

10.对从数据库服务器做相应的设置，指定复制使用的用户，主数据库服务器的IP、端口、以及开始复制的日志文件和位置等

mysql> 
mysql> change master to 
    -> master_host='192.168.90.180',
    -> master_port=3306,
    -> master_user='orl5',
    -> master_password='mysql12345',
    -> master_log_file='mysql-bin.000005',
    -> master_log_pos=107;
Query OK, 0 rows affected (0.03 sec)
mysql> 
mysql>

11.从库启动slave

mysql> 
mysql> start slave
    -> ;
Query OK, 0 rows affected (0.00 sec)
mysql> 
mysql> 
###查看状态
mysql> 
mysql> show processlist \G
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 4
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 12
  State: Waiting for master to send event   ###该状态是正常的状态
   Info: NULL
*************************** 3. row ***************************
     Id: 5
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 527
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
3 rows in set (0.00 sec)
mysql> 
mysql>

12.测试
主库：

(testing)root@localhost [(none)]> 
(testing)root@localhost [(none)]> use test
Database changed
(testing)root@localhost [test]> 
(testing)root@localhost [test]> 
(testing)root@localhost [test]> 
(testing)root@localhost [test]> create table rep_test(id int);
Query OK, 0 rows affected (0.01 sec)
(testing)root@localhost [test]> 
(testing)root@localhost [test]> 
(testing)root@localhost [test]> insert into rep_test values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
(testing)root@localhost [test]> 
(testing)root@localhost [test]>

从库：

mysql> 
mysql> use test;
Database changed
mysql> 
mysql> 
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| rep_test       |
+----------------+
1 row in set (0.00 sec)
mysql> 
mysql> 
mysql> select * from rep_test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
6 rows in set (0.01 sec)
mysql>

主库中创建的表rep_test以及数据已经全部同步到从库中