前期准备工作：
操作系统：
[root@mysql-server ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.5 (Santiago)
两台主机，一台主，一台从：
主：
172.25.90.3
从：
172.25.90.1
下载mysql的rpm包：
mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-devel-5.7.17-1.el6.x86_64.rpm
mysql-community-embedded-5.7.17-1.el6.x86_64.rpm
mysql-community-embedded-devel-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm
mysql-community-test-5.7.17-1.el6.x86_64.rpm
在两台主机上安装这这些包；
# yum install * -y
安装过程中会报错：
Error: Package: mysql-community-test-5.7.17-1.el6.x86_64 (/mysql-community-test-5.7.17-1.el6.x86_64)
Requires: perl(JSON)
需要安装:perl-JSON-2.50-1.el6.rfx.noarch.rpm
下载链接：ftp://ftp.icm.edu.pl/vol/rzm6/linux-dag/redhat/el6/en/x86_64/extras/RPMS/perl-JSON-2.50-1.el6.rfx.noarch.rpm
安装好之后开始启动mysqld服务：
[root@mysql-server ~]# /etc/init.d/mysqld start
测试用户能否登录：
[root@mysql-server ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
如果你也有上面的错误，
则看这篇文章：http://blog.csdn.net/xayddxjsjxywuhui/article/details/73201597
查看数据库的初始化密码：
# grep temp /var/log/mysqld.log
进行安全配置：
# mysql_secure_installation
配置主从复制：
1.配置master
修改配置文件：
# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql # 从给定目录读取数据库文件
socket=/var/lib/mysql/mysql.sock # 为MySQL客户程序与服务器之间的本地通信指定一个套接字文件(Linux下默认是/var/lib/mysql/mysql.sock文件)
innodb_file_per_table=ON # MySQL的Innodb_file_per_table参数控制表空间存放方式，通过配置Innodb_file_per_table参数可把全部表空间存放到ibdata1的方式改为使用独享表空间将表空间分别单独存放。
skip_name_resolve=ON # 不要把地址解析程主机名
log-bin=mysql-bin #打开日志(主机需要打开)，这个mysql-bin也可以自定义，这里也可以加上路径，如：/home/www/mysql_bin_log/mysql-bin
relay_log=relay-bin # relay-log日志记录的是从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后SQL线程会读取relay-log日志的内容并应用到从服务器
binlog-format=row # mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED
log-slave-updates=true # 启用从机服务器上的slave日志功能，使这台计算机可以用来构成一个镜像链(A->B->C)
server-id=3 #mysql的同步的数据中是包含server-id的，用于标识该语句最初是从哪个server写入的，所以server-id一定要有的，一般就写，ip地址的最后一位，比如172.25.90.3，就写3
重启服务
# /etc/init.d/mysqld restart
登录操作
mysql> show master status; #可以查看master数据库当前正在使用的二进制日志及当前执行二进制日志位置
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 | 1783 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
创建帐号（slave来复制时用的身份）
mysql>GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by 'redhat';
配置slave：
修改配置文件：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=mysql-bin # [不是必须]启用二进制日志，当他要给别的服务器的当master时需要写
server-id=1
relay_log=mysql-relay-bin
log_slave_updates=1
read_only=1 # 从库只读，不能写
重启服务
/etc/init.d/mysqld restart
登录操作
mysql> CHANGE MASTER TO MASTER_HOST='172.25.90.3', MASTER_USER='backup', MASTER_PASSWORD='redhat',MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=0;
解释：
MASTER_HOST='172.25.90.3', # 写他要到谁那里同步
MASTER_USER='backup', # 写他到了那以什么身份同步
MASTER_PASSWORD='redhat', # 有了身份，还要有“通行证”
MASTER_LOG_FILE='mysql-bin.000008', # 去同步哪一个文件
MASTER_LOG_POS=0; # 从那个文件的那里开始同步，因为第一次同步所以直接写0
mysql> show slave status\G # 查看我们刚刚写的信息
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.25.90.3
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: No
Slave_SQL_Running: No
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: 4
Relay_Log_Space: 154
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: 3
Master_UUID: 9cb72f3f-50a1-11e7-bbbf-5254009d558e
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql> start slave; # 开始复制
在这一步可能会出错：
错误1：
Slave_IO_Running: No
或者
Slave_IO_Running: Connecting
解决：
1.两台机子的网络通不通
2. 看slave所用的用户，密码，等信息是否正确
3. 在slave上是否可以登录master
# mysql -ubackup -predhat -h172.25.90.3
错误2：
Slave_SQL_Running: No
解决：
1.程序可能在slave上进行了写操作
2.也可能是slave机器重起后，事务回滚造成的.
一般是事务回滚造成的：
mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> startslave ;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.90.3
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000007
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000009
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: 154
Relay_Log_Space: 693
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: 3
Master_UUID: 9cb72f3f-50a1-11e7-bbbf-5254009d558e
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 more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
在master查看：
mysql> show processlist\G;
*************************** 1. row ***************************
Id: 10
User: backup
Host: 172.25.90.1:33719
db: NULL
Command: Binlog Dump
Time: 444
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
*************************** 2. row ***************************
Id: 12
User: backup
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
2 rows in set (0.00 sec)
配置并行复制
在从库上操作即可：
mysql> show processlist; ##可以看到只有一个复制线程在运行
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 6 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 11 | system user | | NULL | Connect | 333 | Waiting for master to send event | NULL |
| 12 | system user | | NULL | Connect | 333 | Slave has read all relay log; waiting for more updates | NULL |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.41 sec)
mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+------------------+
| 6 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)
mysql> show variables like 'slave_parallel_type';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| slave_parallel_type | DATABASE |
+---------------------+----------+
1 row in set (0.00 sec)
mysql> set global slave_parallel_type='logical_clock';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'slave_parallel_type';
+---------------------+---------------+
| Variable_name | Value |
+---------------------+---------------+
| slave_parallel_type | LOGICAL_CLOCK |
+---------------------+---------------+
1 row in set (0.01 sec)
mysql> show variables like 'slave_parallel_workers';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_parallel_workers | 0 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql> set global slave_parallel_workers=4;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'slave_parallel_workers';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_parallel_workers | 4 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.20 sec)
mysql> show processlist; ##此时我们可以看到有很多i/o线程了，配置成功了
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 6 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 13 | system user | | NULL | Connect | 7 | Waiting for master to send event | NULL |
| 14 | system user | | NULL | Connect | 6 | Slave has read all relay log; waiting for more updates | NULL |
| 15 | system user | | NULL | Connect | 7 | Waiting for an event from Coordinator | NULL |
| 16 | system user | | NULL | Connect | 7 | Waiting for an event from Coordinator | NULL |
| 17 | system user | | NULL | Connect | 7 | Waiting for an event from Coordinator | NULL |
| 18 | system user | | NULL | Connect | 7 | Waiting for an event from Coordinator | NULL |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
7 rows in set (0.00 sec)
配置gtid模式的并行复制：
配置master：
修改配置文件：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_file_per_table = ON
server-id=3log-bin=master-bin
# GTID #
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
port=3306
report-host=172.25.90.3
重启服务：
[root@mysql-server ~]# /etc/init.d/mysqld restart
登录操作：
mysql> show global variables like '%gtid%';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-------+
8 rows in set (0.00 sec)
mysql> grant replication slave on *.* to 'gtiduser'@'%' identified by 'redhat';
此时若出现下面错误：
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
则看下面这篇文章
http://blog.csdn.net/xayddxjsjxywuhui/article/details/73321259
配置slave：
修改配置文件：
[mysqld]
datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
log-bin=mysql-bin
log-slave-updates=1
server-id=1
# GTID #
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
port=3306
report-host=172.25.90.1
重启服务：
[root@mysql1 ~]# /etc/init.d/mysqld restart
登录操作：
mysql> change master to master_host='172.25.90.3',master_user='gtiduser',master_password='redhat',master_auto_position=1;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.25.90.3
Master_User: gtiduser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000010
Relay_Log_Pos: 320
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
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: 0
Relay_Log_Space: 0
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: 7bbb95d3-516c-11e7-8f13-5254009d558e
Master_Info_File: mysql.slave_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: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql> start slave;
若出错：
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
执行下面这句：
mysql> reset slave;
再次开启：
mysql> start slave;
Query OK, 0 rows affected (0.43 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.90.3
Master_User: gtiduser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 194
Relay_Log_File: mysql1-relay-bin.000004
Relay_Log_Pos: 409
Relay_Master_Log_File: master-bin.000002
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: 194
Relay_Log_Space: 1119
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: 3
Master_UUID: 7bbb95d3-516c-11e7-8f13-5254009d558e
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 more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 7bbb95d3-516c-11e7-8f13-5254009d558e:1
Executed_Gtid_Set: 7bbb95d3-516c-11e7-8f13-5254009d558e:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql> show processlist; +----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 5 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 6 | system user | | NULL | Connect | 100 | Waiting for master to send event | NULL |
| 7 | system user | | NULL | Connect | 99 | Slave has read all relay log; waiting for more updates | NULL |
| 8 | system user | | NULL | Connect | 100 | Waiting for an event from Coordinator | NULL |
| 9 | system user | | NULL | Connect | 839 | Waiting for an event from Coordinator | NULL |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
5 rows in set (0.00 sec)
可以看到现在有两个线程正在运行
2021
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
