mysql一主多从

master上备份数据库
[root@master ~]# mysqldump --all-databases > /backup/mysql-all-backup-`date +%F-%T`.sql;

将数据库备份倒数slave2端口中：
[root@master ~]# scp /backup/mysql-all-backup-2018-11-15-20\:09\:41.sql 192.168.122.203:/backup/
查看master端口
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |     1017 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

在slave2端口上安装
[root@slave2 ~]# yum -y install mariadb mariadb-server
导入数据库文件
[root@slave2 ~]# mysql -uroot -p < /backup/mysql-all-backup-2018-11-15-20\:09\:41.sql
[root@slave2 backup]# vi /etc/my.cnf

[mysqld]
...
server-id=3
relay_log=mysql-relay-bin
read_only=1
log_bin=mysql-bin
log_slave_updates=1
...
数据库导入完成
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
slave2端口
MariaDB [(none)]>  change master to master_host='192.168.122.201',
    -> master_user='slave',
    -> master_password='123',
    -> master_log_file='mysql-bin.000003',
    -> master_log_pos=1017;
Query OK, 0 rows affected (0.06 sec)

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

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.122.201
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1017
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......
测试现在一主多从关系：
在master端口上：

MariaDB [db1]>  create database test2;
Query OK, 1 row affected (0.00 sec)

MariaDB [db1]>  use test2;
Database changed
MariaDB [test2]> create table t1(id int);
Query OK, 0 rows affected (0.04 sec)
插入数据
MariaDB [test2]> insert into t1 value(4);
Query OK, 1 row affected (0.00 sec)

MariaDB [test2]> insert into t1 value(5);
Query OK, 1 row affected (0.03 sec)

MariaDB [test2]> select * from t1;
+------+
| id   |
+------+
|    4 |
|    5 |
+------+

在数据库db1上插入数据
MariaDB [test2]> use db1;
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 [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| students      |
+---------------+
1 row in set (0.00 sec)

MariaDB [db1]> desc students;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(50) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [db1]> insert into students values(5,'aa'),(6,'bb');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [db1]> select * from students;
+------+----------+
| id   | name     |
+------+----------+
|    1 | xiaoming |
|    2 | xiaohong |
|    3 | tom      |
|    4 | jerry    |
|    5 | aa       |
|    6 | bb       |
+------+----------+
6 rows in set (0.00 sec)




在slave端口上查看：
MariaDB [db1]> use test2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

MariaDB [test2]> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| t1              |
+-----------------+
1 row in set (0.00 sec)

MariaDB [test2]> select * from t1;
+------+
| id   |
+------+
|    4 |
|    5 |
+------+


MariaDB [db1]> select * from students;
+------+----------+
| id   | name     |
+------+----------+
|    1 | xiaoming |
|    2 | xiaohong |
|    3 | tom      |
|    4 | jerry    |
|    5 | aa       |
|    6 | bb       |
+------+----------+
6 rows in set (0.00 sec)



在slave2端口上查看：
MariaDB [db1]> use test2;
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 [test2]> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| t1              |
+-----------------+
1 row in set (0.00 sec)

MariaDB [test2]> select * from t1;
+------+
| id   |
+------+
|    4 |
|    5 |
+------+
2 rows in set (0.00 sec)

查看在master上插入数据到slave2端口上查看效果：
MariaDB [(none)]> use db1;
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 [db1]> select * from students;
+------+----------+
| id   | name     |
+------+----------+
|    1 | xiaoming |
|    2 | xiaohong |
|    3 | tom      |
|    4 | jerry    |
|    5 | aa       |
|    6 | bb       |
+------+----------+
6 rows in set (0.00 sec)