mysql 5.7 主从同步历史数据_mysql5.7搭建主从同步

http://repo.mysql.com/mysql57-community-release-el7.rpm

yum clean all && yum makecache

yum install gcc gcc-c++ openssl-devel mysql mys

systemctl start mysqld&&systemctl enable mysqld

修改 /etc/my.cnf

[root@master ~]# cat /etc/my.cnf

[mysqld]

#

Remove leading # and set to the amount of RAM for the most important data

cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

innodb_buffer_pool_size = 128M

#

Remove leading # to turn on a very important data integrity option: logging

changes to the binary log between backups.

log_bin

#

Remove leading # to set options mainly useful for reporting servers.

The server defaults are faster for transactions and fast SELECTs.

Adjust sizes as needed, experiment to find the optimal values.

join_buffer_size = 128M

sort_buffer_size = 2M

read_rnd_buffer_size = 2M

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

server-id = 1 #唯一

log-bin=mysql-bin 开启 binlog

sync-binlog=1

validate_password_policy=0

validate_password_length=1

修改完重启

systemctl restart mysqld

过滤MySQL初始密码

cat /var/log/mysqld.log | grep ‘password is generated‘

安全加固

mysql_secure_installation

mysql主库做授权配置

mysql –uroot-p

mysql>grant replication slave on . to ‘repl_user‘@‘192.168.60.%‘ identified by ‘密码‘;

查看主库状态

从库搭建mysql5.7

yum clean all && yum makecache && yum install epel* -y

rpm -Uvh http://repo.mysql.com/mysql57-community-release-el7.rpm

yum clean all && yum makecache

yum install gcc gcc-c++ openssl-devel mysql mys

systemctl start mysqld&&systemctl enable mysqld

修改 /etc/my.cnf

[root@slave ~]#vim /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

server-id = 2 和主库不一致即可

log-bin=mysql-bin

validate_password_policy=0

validate_password_length=6

修改完重启

systemctl restart mysqld

过滤MySQL初始密码

cat /var/log/mysqld.log | grep ‘password is generated‘

安全加固

mysql_secure_installation

从库登录mysql

mysql -uroot -p

mysql> set global read_only=1; 设置从库只读

mysql> change master to master_host=‘192.168.60.202‘,master_user=‘repl_user‘,master_password=‘密码‘,master_log_file=‘mysql-bin.000003‘,master_log_pos=154;

mysql>start slave；

从库 IO和SQL线程两个YES状态