环境:
192.168.1.248 HE1 主库
192.168.1.249 HE2 主库
192.168.1.250 HE3 从库
主库授权备份账户
mysql> grant SELECT,RELOAD,SHOW DATABASES,SUPER,LOCK TABLES,REPLICATION CLIENT,SHOW VIEW,EVENT,FILE on *.* to backup@'localhost' identified by 'MANAGER'; mysql> flush privileges;
建立主从复制的用户名和密码,指定哪个IP地址用户使用这个用户可以访问主库
mysql> grant replication client,replication slave on *.* to 'mysync'@'192.168.1.%' identified by 'MANAGER'; mysql> flush privileges;
主库全库备份
[root@HE1 ~]# mysqldump -ubackup -p --single-transaction --databases 248db --master-data=2 >248.sql
[root@HE2 ~]# mysqldump -ubackup -p --single-transaction --databases 249db --master-data=2 >249.sql
拷贝主库备份文件到从库
[root@HE1 ~]# scp -rp 248.sql root@192.168.1.250:/root
[root@HE2 ~]# scp -rp 249.sql root@192.168.1.250:/root
从库还原
[root@HE3 ~]# mysql -uroot -p <248.sql
Enter password:
[root@HE3 ~]# mysql -uroot -p <249.sql
Enter password:
查看主库备份集中的binlog和position偏移量
[root@HE3 ~]# cat 248.sql |grep "CHANGE MASTER TO MASTER_LOG_FILE='"
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000017', MASTER_LOG_POS=581;
[root@HE3 ~]# cat 249.sql |grep "CHANGE MASTER TO MASTER_LOG_FILE='"
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=581;
从库:
在从库配置读取主库的IP地址,复制的用户名和密码,从主库哪个BINLOG文件开始读取,偏移量是多少
MariaDB [(none)]>CHANGE MASTER 'Master248' TO MASTER_HOST='192.168.1.248',MASTER_USER='mysync',MASTER_PASSWORD='MANAGER',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000017',MASTER_LOG_POS=581;
MariaDB [(none)]>CHANGE MASTER 'Master249' TO MASTER_HOST='192.168.1.249',MASTER_USER='mysync',MASTER_PASSWORD='MANAGER',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=581;
开启从库复制开关
MariaDB [(none)]> start slave 'Master248'; MariaDB [(none)]> show slave 'Master248' status\G MariaDB [(none)]> start slave 'Master249'; MariaDB [(none)]> show slave 'Master249' status\G
验证从库状态是否正常主要看下面这两个状态是否为yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | 248db | | 249db | | information_schema | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec)
在248主机上创建表并插入数据
mysql> use 248db; Database changed mysql> create table aixuan1( -> id int(10) unsigned NOT NULL AUTO_INCREMENT, -> text varchar(20) NOT NULL DEFAULT '', -> PRIMARY KEY(id)) -> ENGINE=innodb AUTO_INCREMENT=1 -> DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.15 sec) mysql> mysql> insert into aixuan1(text) values('aa'),('bb'),('cc'),('dd'),('ee'),('ff'); Query OK, 6 rows affected (0.02 sec) Records: 6 Duplicates: 0 Warnings: 0
从库验证
MariaDB [(none)]> use 248db; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [248db]> show tables; +-----------------+ | Tables_in_248db | +-----------------+ | aixuan1 | +-----------------+ 1 row in set (0.00 sec)
在249主机上创建表并插入数据
mysql> use 249db; Database changed mysql> create table helei1( -> id int(10) unsigned NOT NULL AUTO_INCREMENT, -> text varchar(20) NOT NULL DEFAULT '', -> PRIMARY KEY(id)) -> ENGINE=innodb AUTO_INCREMENT=1 -> DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.15 sec) mysql> mysql> insert into helei1(text) values('aaa'),('bbb'),('ccc'),('ddd'),('eee'),('fff'); Query OK, 6 rows affected (0.02 sec) Records: 6 Duplicates: 0 Warnings: 0
从库验证
MariaDB [248db]> use 249db; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [249db]> show tables; +-----------------+ | Tables_in_249db | +-----------------+ | helei1 | +-----------------+ 1 row in set (0.00 sec) MariaDB [249db]> select * from helei1; +----+------+ | id | text | +----+------+ | 1 | aaa | | 2 | bbb | | 3 | ccc | | 4 | ddd | | 5 | eee | | 6 | fff | +----+------+ 6 rows in set (0.00 sec)
至此,MariaDB多源复制搭建成功。