mysql主从集群搭建；(集群复制数据)

1.搭建mysql 5.7环境
chown mysql:mysql -R /data/
groupadd mysql
useradd -g mysql mysql

yum install numactl
rpm -e mysql-libs --nodeps
rpm -ivh mysql-community-common-5.7.21-1.el6.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.21-1.el6.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.7.21-1.el6.x86_64.rpm
rpm -ivh mysql-community-client-5.7.21-1.el6.x86_64.rpm
rpm -ivh mysql-community-server-5.7.21-1.el6.x86_64.rpm

 

2.升级mysql版本

ssh 老集群-10.40.20.58

service mysql stop

scp -r /data/mysql mysql@10.40.20.91:/data/mysql
scp -r /etc/my.cnf mysql@10.40.20.91:/etc/my.cnf
mv /data/mysql/auto.cnf /data/mysql/auto.cnf.bak

chown mysql:mysql -R /data/

service mysqld start

mysql_upgrade -uroot -pxxxxxx(--socket=/var/lib/mysql/mysql_xczx1.sock)

service mysqld restart

在主节点上mysql命令行中执行：
grant replication slave on *.* to 'repl'@'10.40.10.93' identified by 'xxxxxx';
grant replication slave on *.* to 'repl'@'10.40.100.191' identified by 'xxxxxx';

 

3.复制
10.40.10.93 10.40.100.191 安装mysql

10.40.20.91作为主做copy 10.40.10.93 10.40.100.191 修改 /etc/my.cnf的server_id
mysql -uroot -pxxxxxx -e"reset slave all;"
mysql -uroot -pxxxxxx -e"show master status;"

10.40.10.93 10.40.100.191
mysql -uroot -pxxxxxx -e"change master to master_host='10.40.20.91',master_port=3306,master_user='repl',master_password='xxxxxx',master_log_file='db01-k8s-satnew-bin.000006',master_log_pos=154012606;"
mysql -uroot -pxxxxxx -e"start slave;"
mysql -uroot -pxxxxxx -e"show slave status\G"

 

reset slave all
stop slave
start slave

alter table test modify column id int primary key AUTO_INCREMENT;
show binlog events in 'db01-k8s-satnew-bin.000006';