实验篇2（数据库主主、一主多从实验、读写分离amoeba）

1、主主

在之前主从服务器的模型上，将主作为备，备作为主进行授权（192.168.0.152开始是主，现要作为备）

MariaDB [(none)]> grant replication slave on *.* to slave@'192.168.0.152' identified by '123456';

192.168.0.152上进行接收授权：

MariaDB [(none)]> change master to master_user='slave',master_password='123456',master_host='192.168.0.153',master_log_file='mysql-bin.000004',master_log_pos=398;

master_log_pos是一个变化的数，在主服务器上进行查看：

然后在原来的主上，启动slave：

MariaDB [(none)]> start slave;

从而两边的主主数据库，无论是在哪台服务器上创建数据库都会互相同步。

2、一主多从

在主主基础上，在其中一台主数据库服务器上停止slave

MariaDB [(none)]> stop slave;

这一台然后就作为了主数据，再新起一台服务器作为备数据库：

[root@xuexi ~]#  vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql-bin
server-id=30
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

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

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

~
~
~
"/etc/my.cnf" 21L, 601C 已写入                                                                             
[root@xuexi ~]# 
[root@xuexi ~]# 
[root@xuexi ~]# service mariadb start
Redirecting to /bin/systemctl start mariadb.service
[root@xuexi ~]# systemctl enable mariadb.service 
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
[root@xuexi ~]# 
[root@xuexi ~]# 
[root@xuexi ~]# 
[root@xuexi ~]# 
[root@xuexi ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> 
MariaDB [(none)]> 
MariaDB [(none)]> grant replication slave on *.* to slave@'192.168.0.152' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

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

MariaDB [(none)]>  change master to master_user='slave',master_password='123456',master_host='192.168.0.152',master_log_file='mysql-bin.000004',master_log_pos=479;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| a111               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> 

修改相应的my.cnf文件，启动mysql数据库，并在主服务器数据库上进行授权，从服务器数据库进行接收授权，从将新的这台服务器作为备加入数据库集群。

3、多主一从

重点难点在于从服务器配置的量比较多，主服务器配置和之前一样。重点说一下从服务器配置吧。

首先修改my.cnf配置文件：

[root@xuexi mysqla]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql-bin
server-id=30
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

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

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

#M-M-S
[mysqld_multi]
mysqld=/usr/bin/mysqld_safe
mysqladmin=/usr/bin/mysqladmin
log=/tmp/multi.log

[mysqld10]
port=3308
datadir=/var/lib/mysqla/
pid-file=/var/lib/mysqla/mysqld.pid
socket=/var/lib/mysqla/mysql.sock
user=mysql
server-id=30

[mysqld20]
port=3307
datadir=/var/lib/mysqlb/
pid-file=/var/lib/mysqlb/mysqld.pid
socket=/var/lib/mysqlb/mysql.sock
user=mysql
server-id=30
[root@xuexi mysqla]# 

修改完后启动数据库，然后安装

service mariadb restart
mysql_install_db --datadir=/var/lib/mysqla --user=mysql
mysql_install_db --datadir=/var/lib/mysqlb --user=mysql

改变新建的数据库目录下文件的所有者：

chown -R mysql ../mysqla/
chown -R mysql ../mysqlb/

mysqld_multi --defaults-file=/etc/my.cnf start 10
mysqld_multi --defaults-file=/etc/my.cnf start 20

查看服务的启动状态：

登录不同进程下的数据库

mysql -P 3308 -S /var/lib/mysqla/mysql.sock 
mysql -P 3307 -S /var/lib/mysqlb/mysql.sock 

然后接收主数据库给予的授权即可（下面只写的一个进程，另一个进程类似，就不具体展示了）：

MariaDB [(none)]> change master to master_user='slave',master_password='123456',master_host='192.168.0.153',master_log_file='mysql-bin.000004',master_log_pos=630;

后面多进程的主备数据库同步就完成自动同步了

4、Amoeba实现读写分离

实验环境如下：

• 第一步搭建主从服务器，和之前一样；
• 第二步需要配置amoeba服务器，需要进行开源环境安装，中间键服务器上需要安装gcc等开源环境。需要下载amoeba安装包，并进行解压。
• 第三步、第四步涉及环境变量、具体的配置文件配置、权限赋予等等，细节较多就不进行展示了。

       最后通过实验得出，通过登录amoeba中间件的数据库，查看对应的数据库信息，可以看出对于read权限是两台主备轮询交替查看，而对于写权限，只能在主库上进行写操作，备上无法写入。实验现象如下：

      

     此时备库已经停止slave，开启slave后，主库会主动同步数据库到备库。