MySQLStudy之--MySQL集群之mysql主从复制

MySQL Study之--MySQL集群之mysql 主从复制 MySQL 的数据库的高可用性的架构大概有以下几种：集群，读写分离，主备。而后面两种都是通过复制来实现的。下面将简单介绍复制的原理及配置 复制的原理 MySQL 复制基于主服务器在二进制日志中跟踪所有对数据库的更

MySQL Study之--MySQL集群之mysql 主从复制

MySQL 的数据库的高可用性的架构大概有以下几种：集群，读写分离，主备。而后面两种都是通过复制来实现的。下面将简单介绍复制的原理及配置

复制的原理

MySQL 复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存的更新，以便从服务器可以对其数据拷贝执行相同的更新。

将主服务器的数据拷贝到从服务器的一个途径是使用LOAD DATA FROM MASTER语句。请注意LOAD DATA FROM MASTER目前只在所有表使用MyISAM存储引擎的主服务器上工作。并且，该语句将获得全局读锁定。

MySQL 使用3个线程来执行复制功能,其中1个在主服务器上，另两个在从服务器上。当发出START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让它发送记录在其二进制日志中的语句。
主服务器创建一个线程将二进制日志中的内容发送到从服务器。该线程可以识别为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。
从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。
第3个线程是SQL线程，是从服务器创建用于读取中继日志并执行日志中包含的更新。
有多个从服务器的主服务器创建为每个当前连接的从服务器创建一个线程；每个从服务器有自己的I/O和SQL线程。

实施案例：

主服务器（Master）： 从服务器（Slave）：

hostname： rh6 hostname: mysrv

ip_addr： 192.168.8.245 ip_addr: 192.168.8.240

操作系统： RedHat EL6

MySQL： mysql 5.6

1、配置mysql配置文件：

master：

[root@rh6 oracle]# cat /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/tmp/mysql.sock
#socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql-bin
server-id=1

slave：
[root@mysrv bin]# cat /etc/my.cnf

[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
datadir=/usr/local/mysql/data/mysql
log-bin=mysql-bin
server-id=2

2、重新启动mysql server：
master：

[root@rh6 oracle]# mysqld_safe --user=mysql &
[1] 2704
[root@rh6 oracle]# 150630 17:24:17 mysqld_safe Logging to '/var/log/mysqld.log'.
150630 17:24:18 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[root@rh6 oracle]# netstat -an |grep 3306
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      
[root@rh6 oracle]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.4-m7-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

slave：

[root@mysrv bin]# mysqld_safe --user=mysql&
[1] 18191
[root@mysrv bin]# 150630 17:26:43 mysqld_safe Logging to '/usr/local/mysql/data/mysql/mysrv.err'.
150630 17:26:43 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data/mysql
[root@mysrv bin]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.4-m7-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

3、在主服务器上授权从服务器用户访问
master 授权：
mysql> GRANT REPLICATION SLAVE ON *.* to 'tom'@'%' identified by 'oracle';
Query OK, 0 rows affected (0.01 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
29 rows in set (0.00 sec)

mysql> select user,host,password from user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *2447D497B9A6A15F2776055CB2D1E9F86758182F |
| root | rh6 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | rh6 | |
| tom | % | *2447D497B9A6A15F2776055CB2D1E9F86758182F |
+------+-----------+-------------------------------------------+
7 rows in set (0.00 sec)

4、查看master服务器状态
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 | 307 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

5、配置从服务器：
mysql> change master to master_host='192.168.8.245',master_user='tom',master_password='oracle',master_log_file='mysql-bin.,000010',master_log_pos=307;
Query OK, 0 rows affected, 2 warnings (0.07 sec)

启动slave service：
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.8.245
Master_User: tom
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.,000010
Read_Master_Log_Pos: 307
Relay_Log_File: mysrv-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.,000010
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 307
Relay_Log_Space: 114
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: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID:
Master_Info_File: /usr/local/mysql/data/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: 150630 17:37:40
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
1 row in set (0.00 sec)
-----slave I/O启动失败 ，提示“master 和 slave拥有相同的server-id！
错误检查：
master：
mysql> show variables like '%server%';
+---------------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------+
| character_set_server | latin1 |
| collation_server | latin1_swedish_ci |
| innodb_ft_server_stopword_table | |
| server_id | 1 |
| server_uuid | 94adecc3-a6cf-11e4-87f0-080027ac3540 |
+---------------------------------+--------------------------------------+
5 rows in set (0.00 sec)
slave：
mysql> show variables like '%server%';
+---------------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------+
| character_set_server | latin1 |
| collation_server | latin1_swedish_ci |
| innodb_ft_server_stopword_table | |
| server_id | 1 |
| server_uuid | 32d0a37f-1f08-11e5-97e1-080027d5b17c |
+---------------------------------+--------------------------------------+
5 rows in set (0.00 sec)

检查my.cnf文件，发现有两个server-id参数，并且其中一个server-id为1，删除一个server-id=1的配置参数 ！
[root@mysrv ~]# cat /etc/my.cnf
[mysqld]
server-id = 2
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
datadir=/usr/local/mysql/data/mysql
log-bin=mysql-bin

重新启动mysqld 服务：
[root@mysrv ~]# ps -ef |grep mysqld
root 2221 1877 0 17:53 pts/2 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --user=mysql
mysql 2528 2221 0 17:53 pts/2 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/mysql/mysrv.err --pid-file=/usr/local/mysql/data/mysql/mysrv.pid --socket=/tmp/mysql.sock --port=3306
root 2575 1877 0 17:55 pts/2 00:00:00 grep mysqld
[root@mysrv ~]# kill -9 2221 2528
[root@mysrv ~]# ps -ef |grep mysqld
root 2583 1877 0 17:55 pts/2 00:00:00 grep mysqld
[1]+ Killed mysqld_safe --user=mysql
[root@mysrv ~]# mysqld_safe --user=mysql &
[1] 2584
[root@mysrv ~]# 150630 17:56:15 mysqld_safe Logging to '/usr/local/mysql/data/mysql/mysrv.err'.
150630 17:56:16 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data/mysql
[root@mysrv ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.4-m7-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%server%';
+---------------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------+
| character_set_server | latin1 |
| collation_server | latin1_swedish_ci |
| innodb_ft_server_stopword_table | |
| server_id | 2 |
| server_uuid | 32d0a37f-1f08-11e5-97e1-080027d5b17c |
+---------------------------------+--------------------------------------+
5 rows in set (0.00 sec)
继续同步，同步错误：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.8.245
Master_User: tom
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.,000010
Read_Master_Log_Pos: 307
Relay_Log_File: mysrv-relay-bin.000004
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.,000010
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 114
Relay_Log_Space: 114
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: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 94adecc3-a6cf-11e4-87f0-080027ac3540
Master_Info_File: /usr/local/mysql/data/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: 150630 17:57:44
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
1 row in set (0.00 sec)

问题原因：

mysql> change master to master_host='192.168.8.245',master_user='tom',master_password='oracle',master_log_file='mysql-bin.,000010',master_log_pos=307;

---日志名输入错误！
问题解决方法：
mysql> stop slave;
Query OK, 0 rows affected (0.03 sec)
mysql> change master to master_host='192.168.8.245',master_user='tom',master_password='oracle',master_log_file='mysql-bin.000010',master_log_pos=307;
Query OK, 0 rows affected, 2 warnings (0.08 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.8.245
Master_User: tom
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 307
Relay_Log_File: mysrv-relay-bin.000002
Relay_Log_Pos: 267
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 307
Relay_Log_Space: 430
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: 1
Master_UUID: 94adecc3-a6cf-11e4-87f0-080027ac3540
Master_Info_File: /usr/local/mysql/data/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:
1 row in set (0.00 sec)
----------从以上可以看到，同步成功！
同步测试：（master）
mysql> create database prod;
Query OK, 1 row affected (0.09 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| prod |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> create table t1 (id int,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.06 sec)
mysql> insert into t1 values (1,'tom');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values (2,'scott');
Query OK, 1 row affected (0.06 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | tom |
| 2 | scott |
+------+-------+
2 rows in set (0.00 sec)
slave：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| prod |
| test |
+--------------------+
5 rows in set (0.02 sec)
mysql> use prod;
Database changed
mysql> show tables;
+----------------+
| Tables_in_prod |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | tom |
| 2 | scott |
+------+-------+
2 rows in set (0.00 sec)

------至此，mysql主从复制配置完成！