mysql 5.6主从复制_MySQL5.6主从复制

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:平均查询时间