Linux从入门到放弃 数据库主从复制

配置yum源

[nginx]
baseurl = https://mirror.tuna.tsinghua.edu.cn/mysql/yum/mysql57-community-el7/
gpgcheck = 0
name = Mysql_Repo

安装mysql

yum install mysql-community-server

初始化

[root@db01 ~]# mysqld --initialize-insecure --user=mysql  --datadir=/var/lib/mysql

主库配置文件加入

[root@db01 ~]# vim /etc/my.cnf
server-id=1
log_bin=master-bin
log_bin_index=master-bin.index

重启

[root@db01 ~]# systemctl restart mysqld

查看数据库更改参数状况

mysql> show variables like 'log_bin';

创建复制主库数据的用户

mysql> grant replication slave on *.* to repl@'%' identified by '123';

查看用户

mysql> select user,host from mysql.user;

创建库

mysql> create database zh;

删除库

mysql> drop database xxx;

查看库

show databases;

导出数据库主库的数据

[root@db01 ~]# mysqldump -h127.0.0.1 -P3306 -S /var/lib/mysql.sock -A --master-data=2 --single-transaction  -R --triggers >/tmp/full.sql

从数据库配置 # 添加

server-id=2
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index

数据信息导入从库

[root@db02 ~]# scp -rp 172.16.1.51:/tmp/full.sql ./

[root@db02 ~]# mysql < full.sql

或

[root@db02 ~]# mysql
mysql> source /root/full.sql

查看主数据库

[root@db01 ~]# mysql
mysql> show master status \G

[root@db02 ~]# mysql
mysql> CHANGE MASTER TO MASTER_HOST = '172.16.1.51',MASTER_PORT = 3306,MASTER_USER='repl',MASTER_PASSWORD='123',MASTER_LOG_FILE='master-bin.000002',master_log_pos=590;

启动同步

[root@db02 ~]# mysql
mysql> start slave;启动同步

查看

mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes