centos7建主从数据库

安装mysql5.6在上一篇文章

---

一、主从数据库–主上配置

可以先关闭两台主机的防火墙，避免后面遗忘

1、编辑my.cnf配置文件

增加log_bin=linux（启用二进制日志），确定server_id的值（确保与从主机的值不同）

[root@master mysql]# vi /etc/my.cnf
log_bin = linux

# These are commonly set, remove the # and set as required.
basedir = /usr/local/mysql
datadir = /data/mysql
port = 3306
server_id = 5
socket = /tmp/mysql.sock

2、重启mysql服务，并关闭防火墙

[root@master mysql]#systemctl restart mysql
[root@master mysql]#systemctl stop firewalld

3、备份mysql库

[root@master mysql]# mysqldump -uroot mysql>/tmp/mysql.sql

4、创建测试数据的库

[root@master mysql]# mysql -uroot -e "create database sjk"

5、导入数据

[root@master mysql]# mysql -uroot sjk < /tmp/mysql.sql 

6、进入数据库，进行主从配置

[root@master mysql]# mysql -uroot -p123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.43-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> grant replication slave on *.* to 'repl'@192.168.200.15 by '123456';
Query OK, 0 rows affected (0.01 sec)

192.168.200.15为从主机的ip，123456为从主机的数据库密码

7、锁住表，保持表内数据不变

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)

8、显示主机状态

如果my.cnf配置文件没有配置log_bin字段，则查看不到主机状态

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| linuxy.000001 |   645207 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

二、主从数据库–从上配置

1、修改确认my.cnf配置文件

（修改第二台主机的ip，两台server_id不能相同）

[root@localhost ~]# vi /etc/my.cnf
server_id = 15

2、重启mysql服务，关闭防火墙

[root@localhost ~]# systemctl restart mysql
[root@localhost ~]# systemctl stop firewalld

3、将主上的sjk库同步到从上

先在从上创建sjk库，然后将主上的/tmp/mysql.sql文件拷贝到从上，最后倒入sjk库中

• 从主数据库的主机上拷贝文件到从上

[root@master mysql]# scp /tmp/mysql.sql root@192.168.200.15:/tmp/
root@192.168.200.15's password: 
mysql.sql                                                                          100%  630KB 630.1KB/s   00:00

• 创建库，将文件导入

[root@localhost ~]# mysql -uroot -p -e "create databases sjk"
[root@localhost ~]# mysql -uroot -p sjk < /tmp/mysql.sql 
Enter password: 

• 验证：可查看两个主机的数据库内sjk库的内容时候一致

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sjk                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.10 sec)

mysql> use sjk;
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
mysql> show tables;
+---------------------------+
| Tables_in_kei             |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
28 rows in set (0.00 sec)

mysql> 

4、从数据库配置

[root@localhost ~]# mysql -uroot -p123456
mysql>  change master to master_host='192.168.200.5',master_user='repl',master_password='123456',master_log_file='linuxy.000001',master_log_pos=645207;
Query OK, 0 rows affected, 2 warnings (0.12 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

5、查看主从配置

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.5
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: linux1.000001
          Read_Master_Log_Pos: 331
               Relay_Log_File: localhost-relay-bin.000003
                Relay_Log_Pos: 280
        Relay_Master_Log_File: linux1.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: 331
              Relay_Log_Space: 457
              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: 5
                  Master_UUID: 7e2e4d54-34a5-11eb-84d2-000c29a31474
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
两个都是yes则同步正常

6、回到主服务器上解锁表

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

最后可以验证一下数据同步是否正常，可以在主数据库中创建一个库或表或在表中插入一条数据，然后到从数据库中查看是否有插入的那一条。