生猛干货
带你搞定MySQL实战，轻松对应海量业务处理及高并发需求，从容应对大场面试
一主一从的搭建
前置工作
- OS: CentOS 7
- DB：MySQL5.7
131和132已经按照MySQL-CentOS7通过YUM安装MySQL5.7.29完成了MYSQL的安装，并成功启动。
操作步骤
配置 master主服务器
核心配置： 开启binlog , 指定唯一的server ID
mysql的配置文件 /etc/my.cnf ,增加如下配置
代码语言：javascript复制#指定binlog的存储位置，日志格式为二进制
log-bin=/var/lib/mysql/data/binlog/mysql-bin
#[必须]服务器唯一ID，默认是1，多个mysql server，不重复即可
server-id=1
#每次执行写入就与硬盘同步 (如果不配置这个,会等到缓冲区满了自动刷盘, 安全和性能的权衡,配置为1是每次都会刷盘，自然慢一些，自己权衡)
sync-binlog=1
#需要同步的二进制数据库名 (有多个的话，配置多个binlog-do-db)
binlog-do-db=artisan
#只保留7天的二进制日志，以防磁盘被日志占满
expire-logs-days=7
#不备份的数据库(有多个的话，配置多个binlog-ignore-db)
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys
#关闭名称解析(非必须)
skip-name-resolve全部的配置如下：
配置项解读：
查看 bin-log的配置信息
- File: 当前记录bin-log的文件
- Position: 从服务器读取的位置
- Binlog_Do_DB: 需要同步的数据库
- Binlog_Ignore_DB: 忽略的数据库，不同步
Master的数据库中建立一个复制帐户并授权
在 Master 的数据库中建立一个备份帐户(user = artisan4syn,pwd=artisan)：每个 slave 使用标准的 MySQL 用户名和密码连接 master 。
进行复制操作的用户会授予 REPLICATION SLAVE 权限。
代码语言：javascript复制# @后面的IP段，建议仅对从服务的网段进行开放
CREATE USER 'artisan4syn'@'192.168.%.%' IDENTIFIED BY 'artisan';
grant replication slave,replication client on *.* to artisan4syn@'192.168.%.%' identified by 'artisan';
FLUSH PRIVILEGES;[root@artisan ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.7.29-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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> uninstall plugin validate_password;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER 'artisan4syn'@'192.168.%.%' IDENTIFIED BY 'artisan'; # 用户和密码 自定义
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave,replication client on *.* to artisan4syn@'192.168.%.%' identified by 'artisan'; # *.* 所有数据库的所有表
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> 查看用户 ：
代码语言：javascript复制select * from mysql.user a where a.`User` = 'artisan4syn';
重点关注 host user ,
这是啥意思呢？
意思是 授权用户user 从哪台服务器host能够登录 ----------------> 主节点 （主节点上创建的用户嘛） 允许 192.168网段的IP，通过 artisan4syn用户访问 我主节点。
配置 slave从服务器
slave从节点主要的配置为：打开中继日志，指定唯一的 servr ID，设置只读权限
从服务器mysql的配置文件/etc/my.cnf 文件增加如下配置
代码语言：javascript复制#配置server-id，唯一ID号
server-id=2
#开启从服务器二进制日志 (从节点如果后面没有级联的从节点，binlog可以不打开，避免无谓的资源消耗)
#log_bin = /var/lib/mysql/data/binlog/mysql-bin
#[必须开启]打开Mysql中继日志，日志格式为二进制
relay_log = /var/lib/mysql/data/binlog/mysql-relay-bin
#如果salve库名称与master库名相同，使用本配置
replicate-do-db = artisan
#如果master库名[artisan]与salve库名[artisan01]不同，使用以下配置[需要做映射]
#replicate-rewrite-db = artisan[主库名] -> artisan01[从库名]
#如果不是要全部同步[默认全部同步]，则指定需要同步的表
#replicate-wild-do-table=artisan01.t_order
#replicate-wild-do-table=artisan01.t_order_item
#设置只读权限
read_only = 1
#使得更新的数据写进二进制日志中
log_slave_updates = 1 （这个是不是应该为 on 待确认 , 给GTID用的？ 5.7以下的版本 使用GTID必须要开启 ）配置项解读：
启动从服务器(slave)的复制线程
slave 连接 master ，并开始重做 master 二进制日志中的事件
先到 master上执行下
在slave节点上执行如下命令：
代码语言：javascript复制[root@artisan mysql-bin]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.7.29 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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.
# Step1.设置同步关系
mysql> change master to master_host='192.168.18.131', master_user='artisan4syn', master_password='artisan', master_log_file='mysql-bin.000045', master_log_pos=1308;
Query OK, 0 rows affected, 2 warnings (0.41 sec)
# Step2.启动复制线程
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
mysql> start slave 启动可能报错： ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread;run STOP SLAVE IO_THREAD FOR CHANNEL '' first.
----》原因 ：在设置同步关系时，没有先停止slaves线程。 ----》解决办法： 在设置同步关系之前， 先执行下“stop slave;”，以停止slave线程。然后启动线程"start slave;"
参数说明：
代码语言：javascript复制mysql> change master to master_host='192.168.18.131', // master ip
>master_user='artisan4syn', // 创建的复制用户
>master_password='artisan', // 创建的复制用户的密码
>master_log_file='mysql-bin.000045', //master对应的bin-log文件
>master_log_pos=1308; //master对应的position 看看 mysql的log ,启动了2个线程 。
查看从服务器状态
可使用SHOW SLAVE STATUSG;查看从服务器状态,
也可用show processlist G;查看当前复制状态
# 查看查看从节点状态
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.18.131
Master_User: artisan4syn
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000045
Read_Master_Log_Pos: 1308
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000045
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: artisan
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: 1308
Relay_Log_Space: 527
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: 2a9b3cc0-4140-11ea-b350-000c29f66452
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)
ERROR:
No query specified
mysql> 如何确定slave节点的两个线程工作正常，看红框，需要关注的几个点
Slave_IO_Running: Yes //IO线程正常运行
Slave_SQL_Running: Yes //SQL线程正常运行
代码语言：javascript复制mysql> show processlist G;
*************************** 1. row ***************************
Id: 3
User: root
Host: 192.168.18.1:61080
db: NULL
Command: Sleep
Time: 2549
State:
Info: NULL
*************************** 2. row ***************************
Id: 4
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
*************************** 3. row ***************************
Id: 5
User: system user
Host:
db: NULL
Command: Connect
Time: 784
State: Waiting for master to send event
Info: NULL
*************************** 4. row ***************************
Id: 6
User: system user
Host:
db: NULL
Command: Connect
Time: 784
State: Slave has read all relay log; waiting for more updates
Info: NULL
4 rows in set (0.00 sec)
ERROR:
No query specified
mysql> binlog相关的命令
代码语言：javascript复制# 查看是否开启binlog
mysql>show binary logs;
#查看binlog格式：
mysql>show variables like 'binlog_format';
#获取binlog文件列表：
mysql>show binary logs;
#查看当前正在写入的binlog文件：
mysql>show master status;
#查看master上的binlog：
mysql>show master logs;
#只查看第一个binlog文件的内容：
mysql>show binlog events;
#查看指定binlog文件的内容：
mysql>show binlog events in 'mysql-bin.000045';
#清空所有的bin-log：
mysql>reset master;
#生成一个新的binlog：
mysql>flush logs;#用bash查看binlog日志文件：
[root@artisan binlog]# mysqlbinlog mysql-bin.000045
问题记录
核心：根据配置文件中的配置项log-error，找到mysql的日志文件，从日志中发掘报错信息，从而进一步去解决问题。
linux的默认配置文件位于 /etc/my.cnf
代码语言：javascript复制# 过滤空行和注释行
grep -Ev "^$|[#;]" /etc/my.cnf
所以 启停mysql的时候，查看 /var/log/mysqld.log 即可
File ‘/var/lib/mysql/data/binlog/mysql-bin.index’ not found (Errcode: 2 - No such file or directory)
开启binlog后，重启过程中碰到的问题一
配置开启binlog的时候指定了 log-bin
开启binlog后，重启mysql报错 如下
代码语言：javascript复制[root@artisan ~]# tail -f /var/log/mysqld.log
2020-01-28T01:10:48.022822Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
2020-01-28T01:10:48.746119Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-01-28T01:10:48.748077Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29-log) starting as process 114966 ...
mysqld: File '/var/lib/mysql/data/binlog/mysql-bin.index' not found (Errcode: 2 - No such file or directory)
2020-01-28T01:10:48.750840Z 0 [ERROR] Aborting没有创建这个目录， 需要手工创建
代码语言：javascript复制mkdir -p /var/lib/mysql/data/binlog File ‘/var/lib/mysql/data/binlog/mysql-bin.index’ not found (Errcode: 13 - Permission denied)
开启binlog后，重启过程中碰到的问题二
代码语言：javascript复制2020-01-28T01:12:43.463345Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-01-28T01:12:43.467768Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29-log) starting as process 115213 ...
mysqld: File '/var/lib/mysql/data/binlog/mysql-bin.index' not found (Errcode: 13 - Permission denied)
2020-01-28T01:12:43.472386Z 0 [ERROR] Aborting上一步用root 建的 /var/lib/mysql/data/binlog 目录，需要改成mysql用户 ，修改用户和用户组
代码语言：javascript复制chown -R mysql /var/lib/mysql/data/
chgrp -R mysql /var/lib/mysql/data/[ERROR] Unable to setup unix socket lock file.
开启binlog后，重启过程中碰到的问题三
代码语言：javascript复制 2020-01-28T01:21:37.069836Z 0 [Note] Server socket created on IP: '::'.
2020-01-28T01:21:37.069929Z 0 [ERROR] Could not open unix socket lock file /var/lib/mysql/mysql.sock.lock.
2020-01-28T01:21:37.069944Z 0 [ERROR] Unable to setup unix socket lock file.
2020-01-28T01:21:37.069955Z 0 [ERROR] Abortingmysql.sock.lock的用户和用户组改成mysql的
代码语言：javascript复制[root@artisan mysql]# chown mysql:mysql /var/lib/mysql/mysql.sock.lockcan’t read dir of ‘./artisan’ (errcode 13 - permission denied)
开启binlog后，新建artisan数据库，连接artisan,碰到的问题
调整用户和用户组权限
代码语言：javascript复制[root@artisan mysql]# chown -R mysql:mysql artisan/重启mysql
代码语言：javascript复制[root@artisan etc]# ps -ef|grep mysqld |grep -v grep
root 7369 115008 0 14:30 pts/2 00:00:00 tail -f /var/log/mysqld.log
mysql 7419 1 0 14:31 ? 00:00:04 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
[root@artisan etc]#
[root@artisan etc]# 观察日志 /var/log/mysqld.log 启动正常。
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
创建复制账户的时候，密码策略问题
参考：MySQL – ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
更多见官方指导：6.4.3.2 Password Validation Plugin Options and Variables
主从测试
测试主从同步
主节点 ，建表插入数据，测试自动同步
从节点，查看表和数据，
成功。
问题记录
Slave_SQL_Running: No
瞎倒腾了一下，结果把SQL线程搞挂了。
看MYSQL的日志,从节点的表不存在
代码语言：javascript复制2020-01-28T13:18:21.874935Z 6 [ERROR] Slave SQL for channel '': Error executing row event: 'Table 'artisan.t_order' doesn't exist', Error_code: 1146
2020-01-28T13:18:21.875184Z 6 [Warning] Slave: Table 'artisan.t_order' doesn't exist Error_code: 1146
2020-01-28T13:18:21.875207Z 6 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000045' position 1308.是这样的，我同步的那个pos ,并不包含我建库的脚本，所以报错是正常的 。
解决办法： 重新设置了同步关系，重启线程
Master重新查看下
slave节点重新设置同步关系
重新查看
