mysql主从复制

mysql主从复制介绍

MySQL 的主从复制是一个异步的复制过程(但一般情况下感觉是实时同步的),数据库数据从一个 MySQL 数据库(我们称之为 Master)复制到另一个 MySQL 数据库(我们称之为Slave)。 在 Master 与 Slave 之间实现整个主从复制的过程是由三个线程参与完成的。 其中有两个线程(SQL 线程和 IO 线程)在 Slave 端,另外一个线程(IO 线程)在 Master 端。(来自 MySQL 帮助文档)

mysql主从复制配置

master:172.17.0.5

slave:172.17.0.6

注意:主从数据库版本要一致!!!

1、修改主服务器的my.cnf

[root@48ed9c89f5d5 /]# vim /etc/my.cnf
[mysqld]
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 log-bin=mysql-bin    //开启二进制日志
 server-id=1          //服务器ID,必须唯一

2、修改从服务器的my.cnf

[root@54e94ad0e7f8 /]# vim /etc/my.cnf
 [mysqld]
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 log-bin=mysql-bin     //开启二进制日志
 server-id=2           //服务器ID,必须唯一

3、两边的服务都要进行重启

[root@54e94ad0e7f8 /]# systemctl restart mariadb

4、登陆主服务器的mysql建立用户并授权

[root@48ed9c89f5d5 /]# mysql -u root -p
 Enter password: 
 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)]> grant all privileges on . to 'mysync'@'%' identified by 'Aa123456' with grant option;
 Query OK, 0 rows affected (0.00 sec)
 MariaDB [(none)]> flush privileges;
 Query OK, 0 rows affected (0.00 sec)

备注:看资料这一步给slave权限就可以了,但是我的一直起不来,所以我只能给all权限来进行测试了,在此记录下。

5、主服务器查询master的状态

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

记下File和Position号

6、登陆从服务器来配置slave

MariaDB [(none)]> change master to master_host='172.17.0.5',master_user='mysync',master_password='Aa123456',master_log_file='mysql-bin.000001',master_log_pos=481;
 Query OK, 0 rows affected (0.02 sec)

7、检查从服务器的功能状态

MariaDB [(none)]> show slave status\G
 * 1. row *
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 172.17.0.5
                   Master_User: mysync
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 481
                Relay_Log_File: mariadb-relay-bin.000003
                 Relay_Log_Pos: 529
         Relay_Master_Log_File: mysql-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 481
               Relay_Log_Space: 825
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File: 
            Master_SSL_CA_Path: 
               Master_SSL_Cert: 
             Master_SSL_Cipher: 
                Master_SSL_Key: 
         Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error: 
                Last_SQL_Errno: 0
                Last_SQL_Error: 
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 1
 1 row in set (0.00 sec)

第一次不知道怎么没起来,退出mysql重启启动了下服务在回来看就起来了。

加黑的两个状态必须变为yes才行

测试

在主服务器创建数据库插入表测试

MariaDB [(none)]> create database wxj_db;
 Query OK, 1 row affected (0.00 sec)
 MariaDB [(none)]> use wxj_db;
 Database changed
 MariaDB [wxj_db]> create table wxj_tb(id int(3),name char(10));
 Query OK, 0 rows affected (0.00 sec)
 MariaDB [wxj_db]>  insert into wxj_tb values(001,'bobu');
 Query OK, 1 row affected (0.01 sec)
 MariaDB [wxj_db]> show tables;
 +------------------+
 | Tables_in_wxj_db |
 +------------------+
 | wxj_tb           |
 +------------------+
 1 row in set (0.00 sec)

在从服务器上查询是否同步成功

MariaDB [(none)]> show databases;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | test               |
 | wxj_db             |
 +--------------------+
 5 rows in set (0.01 sec)
 MariaDB [(none)]> use wxj_db;
 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 [wxj_db]> show tables;
 +------------------+
 | Tables_in_wxj_db |
 +------------------+
 | wxj_tb           |
 +------------------+
 1 row in set (0.00 sec)
 MariaDB [wxj_db]> select * from wxj_tb;
 +------+------+
 | id   | name |
 +------+------+
 |    1 | bobu |
 +------+------+
 1 row in set (0.00 sec)

默认是同步所有库的,如果有指定的库不需要同步可以在配置文件中添加

不同步哪些数据库
 binlog-ignore-db = mysql  
 binlog-ignore-db = test  
 binlog-ignore-db = information_schema  
 只同步哪些数据库,除此之外,其他不同步
 binlog-do-db = game

可以看到已经同步成功啦,如果不明白的话可以加群交流哦,最下面有QQ群号。

发表评论

登录... 后才能评论