mysql主从复制

主数据库

[root@localhost ~]# ssh-keygen 
[root@localhost ~]# ssh-copy-id root@192.168.122.91
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# setenforce 0

启用二进制日志记录并配置唯一的服务器ID

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log-bin=/var/log/mysql/mysql-bin
server-id=1

创建日志目录并赋予权限

[root@localhost ~]# mkdir /var/log/mysql
[root@localhost ~]# chown mysql.mysql /var/log/mysql

重启服务

[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -uroot -pWww.2.com

要使用新用户root 可以从任何主机上连接到 master 上进行复制操作, 并且用户 root 仅可以使用复制的权限

mysql> grant all  on *.* to repl@'192.168.%' identified by 'Www.2.com';
mysql> grant all on *.* to 'root'@'192.168.%' identified by 'Www.2.com';
mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status \G
mysql> flush privileges;

[root@localhost ~]# systemctl restart mysqld

从服务器

在从服务器上使用刚才的用户进行测试连接

mysql -uroot -p'Www.2.com' -h192.168.104

配置从服务器，并重启

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
server-id=2
[root@localhost ~]# systemctl restart mysqld

假如有数据，先导入数据

[root@localhost ~]#mysqldump --all-databases --master-data=1 -uroot -pWww.2.com -h192.168.122.104 >/dump.db

导入数据

[root@localhost ~]# mysql -uroot -pWww.2.com < /dump.db 
[root@localhost ~]# mysql -uroot -pWww.2.com

Mysql 终端执行连接信息

mysql> change master to
    -> master_host='192.168.122.104',
    -> master_user='root',
    -> master_password='Www.2.com',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=1323; ============vim /dump.db 搜索CHANGE
mysql> start slave;

GTID

主数据库

[root@localhost ~]# ssh-keygen 
[root@localhost ~]# ssh-copy-id root@192.168.122.91
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# setenforce 0

启用二进制日志记录并配置唯一的服务器ID

[mysqld]
log-bin=/var/log/mysql/mysql-bin
server-id=1
gtid_mode=ON
enforce_gtid_consistency=1   # 强制执行GTID一致性。

创建日志目录并赋予权限

[root@localhost ~]# mkdir /var/log/mysql
[root@localhost ~]# chown mysql.mysql /var/log/mysql

重启服务

[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -uroot -pWww.2.com

要使用新用户root 可以从任何主机上连接到 master 上进行复制操作, 并且用户 root 仅可以使用复制的权限

mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.%' identified by 'Www.2.com';
mysql> grant all  on *.* to repl@'192.168.%' identified by 'Www.2.com';
mysql> grant all on *.* to 'root'@'192.168.%' identified by 'Www.2.com';
mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status \G
mysql> flush privileges;

[root@localhost ~]# systemctl restart mysqld

从服务器

在从服务器上使用刚才的用户进行测试连接

[root@localhost ~]# mysql -uroot -p'Www.2.com' -h192.168.122.104

假如有数据，先导入数据

[root@localhost ~]# mysqldump --all-databases --master-data=1 -uroot -pWww.2.com -h192.168.122.104 >/dump.db

从库配置

[root@localhost ~]#  vim /etc/my.cnf
[mysqld]
server-id=2
gtid_mode=ON
enforce_gtid_consistency=1

重启服务

[root@localhost ~]# systemctl restart mysqld

导入数据

[root@localhost ~]# mysql -uroot -pWww.2.com < /dump.db 
[root@localhost ~]#  mysql -uroot -pWww.2.com

Mysql 终端执行连接信息

mysql> CHANGE MASTER TO
    -> MASTER_HOST='172.16.153.10',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='123',
    -> MASTER_AUTO_POSITION=1;
mysql> start slave;
mysql> show slave status

最后编辑于：2019.02.16 17:52:49