Mysql集群讲义

 

 2.主库配置文件 my.conf

(1)开启主从复制，主库的配置
[mysqld]
log-bin = mysql-bin
#指定主库 serverid
server-id=1
#记录同步的数据库，如果不指定则记录全部数据库以及同步全部数据库
binlog-do-db=db_a
(2)执行 SQL 语句查询状态
SHOW MASTER STATUS
3.在主库创建同步用户
(1)授权用户 slave01 使用 123456 密码登录 mysql
grant replication slave on *.* to 'slave01'@'ip 地址' identified by 'kgc';
(2)刷新配置
flush privileges;
4.从库配置文件 my.conf
(1)指定 serverid，只要不重复即可，从库也只有这一个配置，其他都在 SQL 语句中操作
[mysqld]
server-id=2
(2)navicat 中执行同步数据库配置操作：
CHANGE MASTER TO
master_host='127.0.0.1', master_user='slave01', master_password='123456', master_port=3306, master_log_file='mysql-bin.000006', master_log_pos=1120;
(3)启动 slave 同步
START SLAVE;
(4)查看同步状态
SHOW SLAVE STATUS;
5.搭建主库
(1)创建目录
mkdir /data/mysql/master01 -p
cd /data/mysql/master01
mkdir conf data
chmod 777 * -R
(2)创建配置文件
mysql集群之主从复制
cd /data/mysql/master01/conf
vim my.cnf
(3)输入如下内容
[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=1 #服务 id，不可重复
(4)创建容器
docker create --name percona-master01 -v /data/mysql/master01/data:/var/lib/mysql -v
/data/mysql/master01/conf:/etc/my.cnf.d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root
percona:5.7.23
(5)启动
docker start percona-master01 && docker logs -f percona-master01
(6)创建同步账户以及授权(打开 sqlyog 连接数据库)
create user 'kgc'@'%' identified by 'kgc';
grant replication slave on *.* to 'kgc'@'%';
flush privileges;
附注：
#出现 [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and 错误解
决方案，在 my.cnf 配置文件中设置
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISI
ON_BY_ZERO ,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' (7)重新启动
###
docker restart percona-master01 && docker logs -f percona-master01
(8)查看 master 状态
show master status;
(9)查看二进制日志相关的配置项
show global variables like 'binlog%';
(10)查看 server 相关的配置项
show global variables

　　6.搭建从库

(1)创建目录
mkdir /data/mysql/slave01
cd /data/mysql/slave01
mkdir conf data
chmod 777 * -R
(2)创建配置文件
cd /data/mysql/slave01/conf
vim my.cnf
(3)输入如下内容
[mysqld]
server-id=2 #服务 id，不可重复
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_B
Y_ZERO ,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
mysql集群之主从复制
(4)创建容器
docker create --name percona-slave01 -v /data/mysql/slave01/data:/var/lib/mysql -v
/data/mysql/slave01/conf:/etc/my.cnf.d -p 3307:3306 -e MYSQL_ROOT_PASSWORD=root
percona:5.7.23
(5)启动
docker start percona-slave01 && docker logs -f percona-slave01
(6)设置 master 相关信息(navicat 连接 mysql3307 端口)
set sql_mode = '';
set sql_mode = 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';
CHANGE MASTER TO
master_host='192.168.115.188', master_user='kgc', master_password='kgc', master_port=3306, master_log_file='mysql-bin.000002', master_log_pos=648;
(7)启动同步
start slave;
(8)查看 master 状态
show slave status;
(9)测试：
mysql> show slave status *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event Master_Host: 192.168.115.188
Master_User: kgc
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1370
Relay_Log_File: dfcb7c833aeb-relay-bin.000002 Relay_Log_Pos: 1042
Relay_Master_Log_File: mysql-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:
mysql集群之主从复制
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1370
Relay_Log_Space: 1256
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: 444b04d6-249d-11e9-affd-0242ac110002
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)
(10)测试同步库，表，数据
可以进行创建数据库、表，插入数据等操作，可以发现实现了主从