基于mysql5.6的GTID和多线程功能实现主从复制
配置环境
主服务器：192.168.85.144 node1.a.com
从服务器：192.168.85.145 node2.a.com
一.搭建环境
主从服务器上进行如下操作：
1.创建数据存放目录
[root@node1 ~]# mkdir -pv /mysqldata/data
mkdir: created directory `/mysqldata'
mkdir: created directory `/mysqldata/data'
2.创建mysql用户
[root@node1 ~]# useradd -r mysql
3.更改数据存放目录的属主属组为mysql
[root@node1 ~]# chown -R mysql.mysql /mysqldata/data/
4.安装mysql-5.6
[root@node1 ~]# tar xf mysql-5.6.27-linux-glibc2.5-i686.tar.gz -C /usr/local/
[root@node1 ~]# cd /usr/local/
[root@node1 local]# ln -sv mysql-5.6.27-linux-glibc2.5-i686 mysql
`mysql' -> `mysql-5.6.27-linux-glibc2.5-i686'
5.更改mysql目录下的属主属组
[root@node1 mysql]# chown -R root.mysql ./*
6.初始化mysql
[root@node1 mysql]# scripts/mysql-install-db --user=mysql --datadir=/mysqldata/data/
初始化完成后，会在当前目录下生成配置文件my.cnf
7.创建运行所需的文件
[root@node1 mysql]# cp -p my.cnf /etc/
[root@node1 mysql]# cp -p support-files/mysql.server /etc/rc.d/init.d/mysqld
[root@node1 mysql]# chkconfig --add mysqld
二.配置mysql
主服务器上：
1.先启动主服务器的mysql服务
[root@node1 mysql]# sed "/^#/d" /etc/my.cnf | sed "/^$/d"
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
datadir = /mysqldata/data
port = 3306
server_id = 1
socket = /tmp/mysql.sock
log_bin = master_bin
innodb_file_per_table = 1
[root@node1 mysql]# service mysqld start
Starting MySQL.................... SUCCESS!
[root@node1 mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.27_log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
2.启动从服务器的mysql服务
[root@node2 mysql]# sed "/^#/d" /etc/my.cnf | sed "/^$/d"
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
datadir = /mysqldata/data
port = 3306
server_id = 11
socket = /tmp/mysql.sock
log_bin = master_bin
innodb_file_per_table = 1
[root@node2 mysql]# service mysqld start
Starting MySQL.............. SUCCESS!
[root@node2 mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.27_log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
当主从服务器的mysql启动没有问题后，接着进行GTID的配置;
主服务器上：
3.编辑主配置文件，添加如下行
binlog_format=ROW
log_slave_updates=true
gtid_mode=on
enforce_gtid_consistency=true
master_info_repository=TABLE
relay_log_info_repository=TABLE
sync_master_info=1
slave_parallel_workers=2
binlog_checksum=CRC32
master_verify_checksum=1
slave_sql_verify_checksum=1
binlog_rows_query_log_events=1
report_port=3306
report_host=192.168.85.144
然后重启mysql服务
[root@node1 ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL...... SUCCESS!
连到mysql上查看有关GTID的变量：
mysql> show global variables like "%gtid%";
+_________________________________+_______+
| Variable_name | Value |
+_________________________________+_______+
| binlog_gtid_simple_recovery | OFF |
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| simplified_binlog_gtid_recovery | OFF |
+_________________________________+_______+
mysql> show global variables like "%uuid%";
+_______________+______________________________________+
| Variable_name | Value |
+_______________+______________________________________+
| server_uuid | f59d2c35_a7e1_11e5_9442_000c29971c35 |
+_______________+______________________________________+
从服务器上：
4.编辑主配置文件，添加如下行：
binlog_format=ROW
log_slave_updates=true
gtid_mode=on
enforce_gtid_consistency=true
master_info_repository=TABLE
relay_log_info_repository=TABLE
sync_master_info=1
slave_parallel_workers=2
binlog_checksum=CRC32
master_verify_checksum=1
slave_sql_verify_checksum=1
binlog_rows_query_log_events=1
report_port=3306
log_bin=slave_bin #log_bin重新写了
report_host=192.168.85.145
重启mysql服务
[root@node2 mysql]# service mysqld restart
Shutting down MySQL... SUCCESS!
Starting MySQL....... SUCCESS!
mysql> show variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 305a0e6e-a854-11e5-972b-000c2962ebc2 |
+---------------+--------------------------------------+
附上参数的解释：
要在MySQL 5.6中使用复制功能，其服务配置段[mysqld]中应该定义如下选项：
binlog-format：二进制日志的格式，有row、statement和mixed几种类型；
需要注意的是：当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW，现在MySQL官方认为STATEMENT这个已经不再适合继续使用；但mixed类型在默认的事务隔离级别下，可能会导致主从数据不一致；
log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host：用于启动GTID及满足附属的其它需求；
master-info-repository和relay-log-info-repository：启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能
sync-master-info：启用之可确保无信息丢失；
slave-paralles-workers：设定从服务器的SQL线程数；0表示关闭多线程复制功能；
binlog-checksum、master-verify-checksum和slave-sql-verify-checksum：启用复制有关的所有校验功能；
binlog-rows-query-log-events：启用之可用于在二进制日志记录事件相关的信息，可降低故障排除的复杂度；
log-bin：启用二进制日志，这是保证复制功能的基本前提；
server-id：同一个复制拓扑中的所有服务器的id号必须惟一；
report-host：
The host name or IP address of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server.
report-port:
The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration.
master-info-repository:
The setting of this variable determines whether the slave logs master status and connection information to a FILE (master.info), or to a TABLE (mysql.slave_master_info)
relay-log-info-repository：
This option causes the server to log its relay log info to a file or a table.
log_slave_updates：
Whether updates received by a slave server from a master server should be logged to the slave's own binary log. Binary logging must be enabled on the slave for this variable to have any effect.
三.配置主从复制
1.主服务器上创建复制时用的账户
mysql> grant replication slave on *.* to 'repuser'@'192.168.85.145' identified by 'reppass';
2.从服务器连接主服务器
change master to master_host='192.168.85.144',master_user='repuser',master_password='reppass',master_port=3306,master_auto_position=1;
3.启动从服务器的IO_thread和SQL_thread进程，然后查看slave状态
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.85.144
Master_User: repuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 577
Relay_Log_File: node2-relay-bin.000004
Relay_Log_Pos: 789
Relay_Master_Log_File: master-bin.000003
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: 577
Relay_Log_Space: 1245
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: f59d2c35-a7e1-11e5-9442-000c29971c35
Master_Info_File: mysql.slave_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: f59d2c35-a7e1-11e5-9442-000c29971c35:1-4
Executed_Gtid_Set: f59d2c35-a7e1-11e5-9442-000c29971c35:1-4
Auto_Position: 1
四.测试主从复制
主服务器上：
mysql> create database mydb;
从服务器上查看：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
至此，基于GTID实现mysql5.6主从复制已全部实现；
阅读(877) | 评论(0) | 转发(0) |
