CC00096.LinuxNetwork——|Linux&MySQL集群.V04|——|多主一从|

一、MySQL 多主一从

### --- MySQL 多主一从

~~~     主服务器配置
~~~     开启二进制日志，启动服务

### --- 部署规划

~~~     centos6.x server21:20.20.20.21  主服务器1
~~~     centos6.x server22:20.20.20.22  主服务器2
~~~     centos6.x server23:20.20.20.23  从服务器1

二、配置主服务器1

### --- 配置主服务器1

[root@server21 ~]# yum install -y mysql mysql-server
[root@server21 ~]# vim /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql-bin
server-id=21

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[root@server21 ~]# chkconfig mysqld on
[root@server21 ~]# service mysqld start

### --- 授权

[root@server21 ~]# mysql
mysql> grant replication slave on *.* to slave@'20.20.20.23' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      257 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

三、配置主服务器2

### --- 配置主服务器2

[root@server22 ~]# yum install -y mysql mysql-server 
[root@server22 ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql-bin
server-id=22

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[root@server22 ~]# chkconfig mysqld on
[root@server22 ~]# service mysqld start

### --- 授权

[root@server22 ~]# mysql
mysql> grant replication slave on *.* to slave@'20.20.20.23' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      257 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

四、配置从服务器1

### --- 配置从服务器1

[root@server23 ~]# yum install -y mysql mysql-server
[root@server23 ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqld_multi]
mysqld=/usr/bin/mysqld_safe
mysqladmin=/usr/bin/mysqladmin
log=/tmp/multi.log

[mysqld21]
port=3306
datadir=/var/lib/mysqla/
pid-file=/var/lib/mysqla/mysqld.pid
socket=/var/lib/mysqla/mysql.sock
user=mysql
server-id=23                                        # 当前服务器的ID

[mysqld22]
port=3307
datadir=/var/lib/mysqlb/
pid-file=/var/lib/mysqlb/mysqld.pid
socket=/var/lib/mysqlb/mysql.sock
user=mysql
server-id=23                                        # 当前服务器的ID

### --- 初始化数据库，生成目录mysqla,mysqlb

[root@server23 ~]# mysql_install_db --datadir=/var/lib/mysqla --user=mysql
[root@server23 ~]# mysql_install_db --datadir=/var/lib/mysqlb --user=mysql
[root@server23 ~]# ll /var/lib/mysql
mysqla/ mysqlb/

### --- 设置 mysqla,mysqlb 目录及以下文件的属主为 mysql(防止出现权限问题)

[root@server23 ~]# chown -R mysql /var/lib/mysqla/
[root@server23 ~]# chown -R mysql /var/lib/mysqlb/

### --- 启动从服务器线程

[root@server23 ~]# mysqld_multi --defaults-file=/etc/my.cnf start 21
[root@server23 ~]# mysqld_multi --defaults-file=/etc/my.cnf start 22 
[root@server23 ~]# netstat -antp
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      1864/mysqld         
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      1986/mysqld

### --- 分别登录并保存授权信息
~~~     登录3306端口的并保存授权信息(20.20.20.21)

[root@server23 ~]# mysql -uroot -P 3306 -S /var/lib/mysqla/mysql.sock
mysql> change master to
    -> master_user='slave',
    -> master_password='123456',
    -> master_host='20.20.20.21',
    -> master_log_file='mysql-bin.000003',
    -> master_log_pos=257;
Query OK, 0 rows affected (0.05 sec)

~~~     启动从的进程

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

~~~     登录3307端口的并保存授权信息(20.20.20.22)

[root@server23 ~]# mysql -uroot -P 3307 -S /var/lib/mysqlb/mysql.sock 
mysql> change master to
    -> master_user='slave',
    -> master_password='123456',
    -> master_host='20.20.20.22',
    -> master_log_file='mysql-bin.000003',
    -> master_log_pos=257; 
 Query OK, 0 rows affected (0.08 sec)

~~~     启动从的进程

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

五、验证测试

### --- 在主服务器1：20.20.20.21上创建数据库，看从服务器20.20.20.23是否同步数据库
~~~     主服务器1上创建abc

mysql> create database abc;
Query OK, 1 row affected (0.00 sec)

~~~     从服务器上查看

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

~~~     主服务器1上删除abc

mysql> drop database abc;
Query OK, 0 rows affected (0.00 sec)

~~~     从服务器上查看

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

### --- 在主服务器2：20.20.20.22上创建数据库，看从服务器20.20.20.23是否同步数据库
~~~     主服务器2上创建a111；

mysql> create database a111;
Query OK, 1 row affected (0.00 sec)

~~~     从服务器上查看

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| a111               |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

~~~     主服务器2上删除a111；

mysql> drop database a111;
Query OK, 0 rows affected (0.00 sec)

### --- 两个各自接受各自的授权。
~~~     从服务器上查看

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)