环境：
192.168.1.248 HE1 主库
192.168.1.249 HE2 主库
192.168.1.250 HE3 从库
主库授权备份账户
|
1
2
|
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地址用户使用这个用户可以访问主库
|
1
2
|
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文件开始读取，偏移量是多少
|
1
|
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;
|
|
1
|
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;
|
开启从库复制开关
|
1
2
3
4
5
6
7
|
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
|
1
2
3
4
5
6
7
8
9
10
11
12
|
MariaDB [(none)]> show databases;
+
--------------------+
|
Database
|
+
--------------------+
| 248db |
| 249db |
| information_schema |
| mysql |
| performance_schema |
| test |
+
--------------------+
6
rows
in
set
(0.00 sec)
|
在248主机上创建表并插入数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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
|
从库验证
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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主机上创建表并插入数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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
|
从库验证
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
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多源复制搭建成功。
本文转自 dbapower 51CTO博客，原文链接：http://blog.51cto.com/suifu/1830682，如需转载请自行联系原作者
