mysql的主从配置
下载最新mysql 的yum源
1、wget https://dev.mysql.com/get/mysql57-community-release-el6-11.noarch.rpm
安装最新mysql
rpm -ivh mysql57-community-release-el6-11.noarch.rpm yum -y install mysql-server
启动mysql数据库
service mysqld start
提示：由于5.7初始化会自动生成密码 ：
cat /var/log/mysqld.log 2018-03-24T13:19:50.925206Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp
server option (see documentation for more details). 2018-03-24T13:19:54.106661Z 0 [Warning] InnoDB: New log files created, LSN=45790 2018-03-24T13:19:54.588874Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2018-03-24T13:19:54.805786Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been
started. Generating a new UUID: 0abbeb4b-2f66-11e8-81f3-000c292c7cea. 2018-03-24T13:19:54.810834Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2018-03-24T13:19:54.841042Z 1 [Note] A temporary password is generated for root@localhost: :kufY//k0zk& (这是随机生成的密码)
登录mysql数据库并修改密码：
mysql -uroot -p ALTER USER 'root'@'localhost' IDENTIFIED BY 'Xu19920615!@';
1.配置master的配置文件my.cnf
#打开日志(主机需要打开) log-bin=mysql-bin ##服务器id（这个是唯一的） server-id=1 #给从机同步的库(可以写多个库) binlog-do-db=mydb binlog-do-db=mydb2 binlog-do-db=test #自动清理5天前的log文件 expire_logs_days=5
2.修改从服务器的从数据库slave /etc/my.cnf配置
#服务器id server-id=2 ##要从主机同步的库 replicate-do-db=mydb replicate-do-db=mydb2 replicate-do-db=test
3.修改之后，重启MySQL主数据库和MySQL从数据库的服务
service mysqld restart
4.配置主服务器的主数据库
主数据库授权同步账户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.10.115' IDENTIFIED BY 'Xu19920615!@'; Query OK, 0 rows affected, 1 warning (0.01 sec)
刷新权限
FLUSH PRIVILEGES;
查看主服务状态
mysql> show master status ; +------------------+----------+-----------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+-----------------+------------------+-------------------+ | mysql-bin.000001 | 602 | mydb,mydb2,test | | | +------------------+----------+-----------------+------------------+-------------------+ 1 row in set (0.00 sec)
5.配置从服务器的从数据库
mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.116',
-> MASTER_USER='root',
-> MASTER_PASSWORD='Xu19920615!@',
-> MASTER_LOG_FILE='mysql-bin.000001', （这个都是根据主服务器查询出的结果）
-> MASTER_LOG_POS=602;（就是主服务器 show master status；）
Query OK, 0 rows affected, 2 warnings (0.13 sec)
开启SLAVE同步
start slave;
查看下slave状态
show slave status G;
mysql> show slave status G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.116
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 602
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes （这个位置显示yes）
Slave_SQL_Running: Yes （这个位置也显示yes表示主从复制配置成功）
Replicate_Do_DB: mydb,mydb2,test
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: 602
Relay_Log_Space: 531
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: 0abbeb4b-2f66-11e8-81f3-000c292c7cea
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:
1 row in set (0.00 sec)
当Slave_IO_Running和Slave_SQL_Running都为Yes，才说明主从复制成功
6.停止SLAVE同步
stop slave;
7.撤销已经赋予给MySQL同步账户的权限
revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可：
GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.10.116' IDENTIFIED BY 'XXXXXX';
REVOKE REPLICATION SLAVE ON *.* FROM 'root'@'192.168.10.116';
8.授权账号可以远程登录
GRANT ALL ON *.* TO 用户名@'%' IDENTIFIED BY '密码' WITH GRANT OPTION;
多线程复制
Mysql 5.7 主从复制的多线程复制配置方式，数据库复制的主要性能问题就是数据延时，为了优化复制性能，Mysql 5.6 引入了 “多线程复制” 这个新功能，但 5.6 中的每个线程只能处理一个数据库，所以如果只有一个数据库，或者绝大多数写操作都是集中在某一个数据库的，那么这个“多线程复制”就不能充分发挥作用了，Mysql 5.7 对 “多线程复制” 进行了改善，可以按照逻辑时钟的方式来分配线程，大大提高了复制性能，下面看一下在5.7中如何配置 “多线程复制”
1、对两个mysql 实例配置好主从复制配置成功后，在从库上使用
show processlist
查看现在的状态
1、可以看到只有一个复制线程在运行
mysql> show processlist; +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+ | 20 | system user | | NULL | Connect | 3048 | Slave has read all relay log; waiting for more updates | NULL | | 25 | root | localhost | mysql | Query | 0 | starting | show processlist | +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+ 3 rows in set (0.00 sec)
2、在从库上停止复制
mysql> stop slave; Query OK, 0 rows affected (0.00 sec)
3、设置并发同步类型为逻辑时钟方式，先看下现在 slave 的并发类型，通过变量 slave_parallel_type 的值来获得，这个变量用来决定如何使用多线程复制
mysql> show variables like 'slave_parallel_type'; +---------------------+----------+ | Variable_name | Value | +---------------------+----------+ | slave_parallel_type | DATABASE | +---------------------+----------+ 1 row in set (0.01 sec)
默认是datebase，每个线程只能处理一个数据库
配置成基于逻辑时钟的方式
mysql> set global slave_parallel_type='logical_clock'; Query OK, 0 rows affected (0.00 sec)
4、设置复制线程的数量
先看下当前的并发数量，通过变量 slave_parallel_workers 的值来获得，这个变量用来决定并发处理的线程数
mysql> show variables like 'slave_parallel_workers'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | slave_parallel_workers | 0 | +------------------------+-------+ 1 row in set (0.01 sec)
现在是 0，我们把他改成 4
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.01 sec)
5、启动复制
mysql> start slave; Query OK, 0 rows affected (0.03 sec)
6、验证配置结果
mysql> show processlist; +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+ | 25 | root | localhost | mysql | Query | 0 | starting | show processlist | | 26 | system user | | NULL | Connect | 5 | Waiting for master to send event | NULL | | 27 | system user | | NULL | Connect | 5 | Slave has read all relay log; waiting for more updates | NULL | | 28 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL | | 29 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL | | 30 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL | | 31 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL | +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+ 7 rows in set (0.00 sec)