一、主服务器1的配置
1、数据库初始化
|
1
2
3
4
5
6
|
[root@bogon ~]# groupadd -r mysql
[root@bogon ~]# mkdir -p /mydata/data
[root@bogon ~]# useradd -g mysql -r -s /sbin/nologin mysql
[root@bogon ~]# chown -R mysql:mysql /mydate
[root@bogon mysql]# chown mysql.root * -R
[root@bogon mysql]# scripts/mysql_install_db --user=mysql --databasedir=/mydata/data/
|
2、准备配置文件和服务器脚本
|
1
2
|
[root@bogon mysql]# cp support-files/my-large.cnf /etc/my.cnf
[root@bogon mysql]# cp support-files/mysql.server /etc/init.d/mysqld
|
3、修改主服务器1的配置文件
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
确保在[mysqld]中有以下配置选项
thread_concurrency =
2
线程数为cpu个数*
2
datadir=/mydata/data
数据目录的位置
innodb_file_per_table = ON
开启InnoDB数据表单独存放的功能
log-bin=/mydata/logs/master-bin
二进制日志存储位置及命名方式
relay-log=relay-mysql
中继日志的存储位置及命名方式
auto-increment-offset=
1
自动增长类型的初始值
auto-increment-increment=
2
自动增长类型的步长
server-id =
100
服务器的id号
|
4.授权主服务器2有复制权限
|
1
|
MariaDB [(none)]> grant replication slave,replication client on *.* to
'wangfeng7399'
@
'192.168.1.114'
identified by
'wangfeng7399'
;
|
5.查看主服务器1的二进制日志文件和记录位置
|
1
2
3
4
5
6
|
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.
000002
|
538
| | |
+-------------------+----------+--------------+------------------+
|
二、主服务器2的配置
1.初始化数据库
|
1
2
3
4
5
6
|
[root@bogon ~]# groupadd -r mysql
[root@bogon ~]# mkdir -p /mydata/data
[root@bogon ~]# useradd -g mysql -r -s /sbin/nologin mysql
[root@bogon ~]# chown -R mysql:mysql /mydate
[root@bogon mysql]# chown mysql.root * -R
[root@bogon mysql]# scripts/mysql_install_db --user=mysql --databasedir=/mydata/data/
|
2.准备配置文件和服务器脚本
|
1
2
|
[root@bogon mysql]# cp support-files/my-large.cnf /etc/my.cnf
[root@bogon mysql]# cp support-files/mysql.server /etc/init.d/mysqld
|
3.修改主服务器2的配置文件
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
确保在[mysqld]中有以下配置选项
thread_concurrency =
2
线程数为cpu个数*
2
datadir=/mydata/data
数据目录的位置
innodb_file_per_table = ON
开启InnoDB数据表单独存放的功能
log-bin=/mydata/logs/master-bin
二进制日志存储位置及命名方式
relay-log=relay-mysql
中继日志的存储位置及命名方式
auto-increment-offset=
2
自动增长类型的初始值
auto-increment-increment=
2
自动增长类型的步长
server-id =
200
服务器的id号
|
4.授权主服务器1能够有复制权限
|
1
|
MariaDB [(none)]> grant replication slave,replication client on *.* to
'wangfeng7399'
@
'192.168.1.112'
identified by
'wangfeng7399'
;
|
5.查看主服务器2的二进制日志文件和记录位置
|
1
2
3
4
5
6
|
MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| master1-bin.
000003
|
558
| | |
+--------------------+----------+--------------+------------------+
|
三、两个服务器建立连接
1.主服务器1与主服务器2建立连接
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
MariaDB [(none)]> change master to master_host=
'192.168.1.114'
,master_user=
'wangfeng7399'
,master_password=
'wangfeng7399'
,master_log_file=
'master1-bin.000003'
,master_log_pos=
558
;
启动复制线程
MariaDB [(none)]> start slave;
查看服务器状态
MariaDB [(none)]> show slave status\G
***************************
1
. row ***************************
Slave_IO_State: Waiting
for
master to send event
Master_Host:
192.168
.
1.114
Master_User: wangfeng7399
Master_Port:
3306
Connect_Retry:
60
Master_Log_File: master1-bin.
000003
Read_Master_Log_Pos:
558
Relay_Log_File: bogon-relay-bin.
000002
Relay_Log_Pos:
537
Relay_Master_Log_File: master1-bin.
000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
可以看到我们的线程都已经启动了
|
2.主服务器2与主服务器1建立连接
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
MariaDB [(none)]> change master to master_host=
'192.168.1.112'
,master_user=
'wangfeng7399'
,master_password=
'wangfeng7399'
,master_log_file=
'master-bin.000002'
,master_log_pos=
538
;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
***************************
1
. row ***************************
Slave_IO_State: Waiting
for
master to send event
Master_Host:
192.168
.
1.112
Master_User: wangfeng7399
Master_Port:
3306
Connect_Retry:
60
Master_Log_File: master-bin.
000002
Read_Master_Log_Pos:
538
Relay_Log_File: relay-mysql.
000002
Relay_Log_Pos:
536
Relay_Master_Log_File: master-bin.
000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
可以看到线程启动成功
|
四、测试
1.在任意一个主服务器上创建一个数据库mydb，本处，我们在主服务器1上创建
|
1
2
3
4
5
6
7
8
9
10
11
|
MariaDB [(none)]> create database mydb;
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
|
2.在主服务器2上查看
|
1
2
3
4
5
6
7
8
9
10
|
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
|
3.在主服务器2上创建表t1，字段为id（自动增长，主键，不为空）name（20个字符），并添加数据zhangsan和lisi
|
1
2
|
MariaDB [(none)]> create table mydb.t1(id
int
unsigned not
null
primary key auto_increment,name char(
20
));
MariaDB [(none)]> insert into mydb.t1(name) values (
'zhangsan'
),(
'lisi'
);
|
4.在主服务器1上t1表中，添加wusong和luzhishen
|
1
|
MariaDB [(none)]> insert into mydb.t1(name) values (
'wusong'
),(
'luzhishen'
);
|
5.在主服务器1上查询
|
1
2
3
4
5
6
7
8
9
|
MariaDB [(none)]> select * from mydb.t1;
+----+-----------+
| id | name |
+----+-----------+
|
1
| wusong |
|
2
| zhangsan |
|
3
| luzhishen |
|
4
| lisi |
+----+-----------+
|
6.在服务器2上查询
|
1
2
3
4
5
6
7
8
9
|
MariaDB [(none)]> select * from mydb.t1;
+----+-----------+
| id | name |
+----+-----------+
|
1
| wusong |
|
2
| zhangsan |
|
3
| luzhishen |
|
4
| lisi |
+----+-----------+
|
大功告成，如有错误，请各位大神多多批评指正，谢谢
本文转自wangfeng7399 51CTO博客，原文链接：http://blog.51cto.com/wangfeng7399/1395024，如需转载请自行联系原作者
