1.原理
1、主服务器开启二进制日志,将改变记录到二进制日志中。
2、从服务器开启中继日志线程,将读取到主服务器的二进制日志记录到中继日志中。
3、从服务器重做中继日志的事件,将改变反应到从服务器中,完成主从复制。
2.环境
操作系统: CentOS Linux release 7.6.1810 (Core)
内核:3.10.0-957.el7.x86_64
MySQL版本:mysql-community-server-5.6.44-2.el7.x86_64.rpm
主服务器ip:172.168.1.62
从服务器ip:172.168.1.63
3.MySQL安装
3.1 配置yum源
wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
3.2 安装MySQL
yum repolist all | grep mysql
yum install mysql-community-server
3.3 MySQL配置文件
cp /etc/my.cnf{,$(date +%F).bak}
cat /etc/my.cnf
[mysqld]
user = mysql
port = 3306
open_files_limit = 2048
back_log = 600
max_connections = 800
max_connect_errors = 3000
max_allowed_packet =8M
lower_case_table_names=1
datadir=/mysql/3306/data
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
default-storage-engine=InnoDB
innodb_open_files=5000
innodb-file-per-table=1
max_connections=1000
max_user_connections=0
log_bin=/mysql/3306/log-bin/mysql_bin
server-id=1
binlog-ignore-db=mysql
wait_timeout = 864000
interactive_timeout = 864000
[mysqld_safe]
log-error=/mysql/3306/logs/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
mkdir /mysql/3306/{data,logs,log-bin} -p
chown mysql:mysql -R /mysql/3306/{data,logs,log-bin}
3.4 启动MySQL
systemctl start mysql
4.配置主服务器
4.1 修改配置文件,开启bin_log日志。
[mysqld]
log_bin=mysql_bin #开启bin_log日志
server-id=1 #服务器唯一ID
binlog-ignore-db=mysql #禁止同步的数据库
重启MySQL
systemctl restart mysql
4.2 登入MySQL配置授权用户。
grant replication slave on *.* to 'slave'@'172.168.1.%' identified by '123456';
flush privileges;
mysql> select user,host from mysql.user;
+-------+-------------+
| user | host |
+-------+-------------+
| root | 127.0.0.1 |
| slave | 172.168.1.% |
| root | localhost |
+-------+-------------+
4.3 查看数据库数据状态。
锁库:flush table with read lock;
show master status
mysql> show master status; # 标红为从服务器开始同步主服务器的位置
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 | 1071 | | mysql | |
+------------------+----------+--------------+------------------+-------------------+
4.4 导出主库的的数据。在配置从服务器完成前,不要再操作主服务器。将导出的数据拷贝到从服务器。
mysqldump -uroot -B -A --events -x > /mnt/all.sql
scp /mnt/all.sql 172.168.1.63:/mnt/
4.5 备份完成后,解锁。
unlock tables;
5.配置从服务器
5.1 修改配置文件。
[mysqld]
log_bin=mysql_bin #可写可不写
server-id=2 #服务器唯一的ID
重启服务器
systemctl restart mysql
5.2 将主服务器的数据导入从服务器。确保主从服务器的数据一致性。
mysql -uroot < /mnt/all.sql
5.3 导入从库,配置从服务器连接。
change master to master_user='slave',\
master_host='172.168.1.62',\
master_password='123456',\
master_log_file='mysql_bin.000001',\
master_log_pos=1071;
说明:
master_user='slave', 同步的用户。
master_host='172.168.1.62', 指定主服务器。
master_password='123456', 同步用户密码。
master_log_file='mysql_bin.000001', 开始同步的日志文件。
master_log_pos=1071; 复制读取位置。
5.4 查看从库状态
show slave status\G;
这两个值等于yes 证明主从同步正常。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
6.慢查询
6.1 MySQL慢查询说明
慢查询是用于记录MySQL的SQL执行慢的记录,可以记录tables中也可以记录日志文件中。
SQL在一定时间内未执行完成SQL语句。long_query_time默认10s ,默认开启慢查询,影响性能。
6.2 配置慢查询
slow_query_log= 1表示开启,2表示关闭。
slow-query-log-file= 慢查询的记录到文件path和file—name
long_query_time= 执行时间超过这个时间将会记录到慢查询,默认10S
log_queries_not_using_indexes= 未建立索引的慢查询也进行记录。 1 为开启。
log_output="FILE,TABLE" 同时记录到慢查询文件中和数据库表中。表的位置mysql库slow_log表。
6.3 临时配置
查看慢查询配置
show variables like '%slow_query_log%';
临时开启慢查询
set global slow_query_log=1;
查看触发慢查询的时间
show variables like 'long_query_time%';
临时设置慢查询触发时间
set global long_query_time=4;
测试
select sleep(3);
tail -fn 10 mysql_slow.log
临时切换记录方式
set global log_output='TABLE';
set global log_output='FILE';
查看慢查询表
select * from mysql.slow_log
6.4 分析慢查询日志
-s 排序方式 -t 排行
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log得到返回记录集最多的10个SQL。
c: 访问计数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间