主从复制

 

1）安装mariadb
[root@master ~]#  yum install -y mariadb mariadb-server
[root@slave ~]#  yum install -y mariadb mariadb-server
2）修改master配置文件
[root@master ~]# vim /etc/my.cnf
[mysqld]
...
server-id=1
log-bin=mysql-bin
...

3）启动master服务
[root@master ~]# systemctl start mariadb
[root@master ~]# mysql
4）查看日志
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      245 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
4）创建并授权slave mysql 用的复制帐号
MariaDB [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@'192.168.%.%' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.00 sec)

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


5） 切换到slave端

修改slave配置文件
[root@slave ~]# vim /etc/my.cnf
[mysqld]
...
server-id=2
relay_log=mysql-relay-bin
read_only=1
log_bin=mysql-bin
log_slave_updates=1
...
6）启动slave服务
[root@slave ~]# systemctl start mariadb
7）启动从服务器复制线程，让slave连接master，并开始重做master二进制日志中的事件
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=245;
Query OK, 0 rows affected (0.05 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: 413
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 697
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

7）在master端上创建数据库以及创建表和插入数据
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> use db1;
Database changed
MariaDB [db1]>  create table students(id int , name char(50));
Query OK, 0 rows affected (0.01 sec)

MariaDB [db1]>  insert into students values(1,'xiaoming'),(2,'xiaohong');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [db1]> insert into students values(3,'tom'),(4,'jerry');
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    |
+------+----------+
4 rows in set (0.00 sec)


8）在slave端口查看数据库内容
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

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]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| students      |
+---------------+
1 row in set (0.00 sec)

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