server1为master，server2和server3为slave
server1：(1)编辑mysql主配置文件，设置server-id
vim /etc/my.cnf [mysqld] datadir=/data/mysql socket=/data/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks # symbolic-links=0 # # Settings user and group are ignored when systemd is used. # # If you need to run mysqld under a different user or group, # # customize your systemd unit file for mariadb according to the # # instructions in http://fedoraproject.org/wiki/Systemd # # #default-character-set = utf8 character-set-server = utf8 collation-server = utf8_general_ci server-id=1 log-bin=mysql-bin
(2)重启mysql数据库并查看主机状态
/etc/init.d/mysqld restart mysql -p 输入密码 ##登陆数据库 在mysql中 show master status;
(3)创建mysql用户并授权
CREATE USER 'repl'@'%' IDENTIFIED BY 'westos' #创建用户repl可以通过任何方式登陆，密码为westos GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; #授权
server2:
(1)将mysql添加到环境变量中
vim ~/.bash_profile
source ~/.bash_profile ##重新读取文件
(2)创建用户mysql和数据目录/data/mysql，并修改/data/mysql的权限
chmod 750 /data/mysql
(3)编辑mysql配置文件
[mysqld] datadir=/data/mysql socket=/data/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd character-set-server = utf8 collation-server = utf8_general_ci
(3)初始化mysql
mysqld -initialize --user=mysql
得到临时密码
方便启动mysql
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld /etc/init.d/mysqld start #开启mysql
使用临时密码安全初始化mysql
mysql_secure_installation
(4)添加server id
[mysqld] datadir=/data/mysql socket=/data/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks # symbolic-links=0 # # Settings user and group are ignored when systemd is used. # # If you need to run mysqld under a different user or group, # # customize your systemd unit file for mariadb according to the # # instructions in http://fedoraproject.org/wiki/Systemd # # #default-character-set = utf8 character-set-server = utf8 collation-server = utf8_general_ci server-id=2
(5)重启mysql
/etc/init.d/mysqld restart
(6)以repl用户身份登陆server1中的mysql，测试
mysql -h 172.25.9.1 -u repl -p
server1：
(1)创建库表并插入数据
create database westos; #创建库
mysql> create table westos.user( #创建表
-> username varchar(10));
INSERT INTO westos.user VALUES('user1'); #插入数据
select * from user; #查看数据
(2)将主机被操作的库备份并发送给server2
mysqldump -p westos > dbdump.db scp dbdump.db server2:~
server2：
(1)将备份文件导入数据库
mysql -pwestos westos < dbdump.db
(2)进入mysql设置master
CHANGE MASTER TO MASTER_HOST='172.25.9.1',MASTER_USER='repl',MASTER_PASSWORD='westos',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;
(2)开启slave并查看slave状态
start slave; show slave statusG;
slave_IO_Running和Slave_SQL_Running都是yes即代表配置成功
二、Gtid实现主从复制server1：
(1)修改配置文件，打开gtid模式并重启
vim /etc/my.cnf
/etc/init.d/mysqld restart
server2:
(1)修改配置文件，打开gtid模式并重启
/etc/init.d/mysqld restart
(2)关闭slvae，配置参数，再开启slave，查看slave状态
stop slave; CHANGE MASTER TO MASTER_HOST = '172.25.9.1', MASTER_USER='repl', MASTER_PASSWORD = 'westos', MASTER_AUTO_POSITION=1; start slave; show slave statusG;三、mysql半同步复制
server1：
(1)安装master模块
INSTALL PLUGIN rpl_semi_sync_master SonAME 'semisync_master.so'; #安装master模块
SELECt PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS
WHERe PLUGIN_NAME LIKE '%semi%'; #查询模块状态
(2)启动master模块
SET GLOBAL rpl_semi_sync_master_enabled =1; #启动模块 show variables like 'rpl%'; #查看复制
等待最长响应时间为10s(10000ms)
server2：
(1)安装半同步复制模块
INSTALL PLUGIN rpl_semi_sync_slave SonAME 'semisync_slave.so';
SELECt PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS
WHERe PLUGIN_NAME LIKE '%semi%';
(2)启动模块
SET GLOBAL rpl_semi_sync_slave_enabled =1; #启动模块
(3)开启slave
START SLAVE IO_THREAD;
(4)查看slave状态
show status like 'rpl%' 查看状态
Value为ON即表示成功
四、组复制先将server1，server2，server3中的mysql全部进行初始化
server1：
(1)修改配置文件并重启mysql
vim /etc/my.cnf disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" server_id=3 gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON log_bin=binlog binlog_format=ROW plugin_load_add='group_replication.so' transaction_write_set_extraction=XXHASH64 group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" group_replication_start_on_boot=off group_replication_local_address= "172.25.4.3:33061" group_replication_group_seeds= "172.25.4.1:33061,172.25.4.2:33061,172.25.4.3:33061" group_replication_bootstrap_group=off group_replication_ip_whitelist="172.25.4.0/24,127.0.0.1/8" group_replication_single_primary_mode=OFF group_replication_enforce_update_everywhere_checks=ON group_replication_allow_local_disjoint_gtids_join=ON
/etc/init.d/mysqld restart
(2)安装组复制模块并查看
INSTALL PLUGIN group_replication SonAME 'group_replication.so'; SHOW PLUGINS;
(3)关闭二进制文件写入，创建用户并且授权，完成后打开二进制文件写入
SET SQL_LOG_BIN=0; CREATE USER rpl_user@'%' IDENTIFIED BY 'westos'; GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%'; SET SQL_LOG_BIN=1;
(4)设置master并刷新
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery'; FLUSH PRIVILEGES;
(5)设置相关参数
SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=OFF;
(6)查看模块状态，只有状态为ONLINE才能实现组复制
SELECT * FROM performance_schema.replication_group_members; # 查看server2，server3：
(1)关闭mysql
/etc/init.d/mysqld stop
(2)修改mysql配置文件
#default-character-set = utf8 character-set-server = utf8 collation-server = utf8_general_ci disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" server_id=2 gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON log_bin=binlog binlog_format=ROW plugin_load_add='group_replication.so' transaction_write_set_extraction=XXHASH64 group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" group_replication_start_on_boot=off group_replication_local_address= "172.25.9.2:33061" group_replication_group_seeds= "172.25.9.1:33061,172.25.9.2:33061,172.25.9.3:33061" group_replication_bootstrap_group=off group_replication_ip_whitelist="172.25.9.0/24,127.0.0.1/8" group_replication_single_primary_mode=OFF group_replication_enforce_update_everywhere_checks=ON group_replication_allow_local_disjoint_gtids_join=ON
注意server3中 server-id=3 local_address="172.25.9.3"
然后初始化mysql
(3)开启mysql并设置相关参数，设置master
SET SQL_LOG_BIN=0; CREATE USER rpl_user@'%' IDENTIFIED BY 'westos'; GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%'; ##授权 SET SQL_LOG_BIN=1; CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';
(4)开启组复制模块
START GROUP_REPLICATION;
server1：
(1)查看
SELECT * FROM performance_schema.replication_group_members;
