ArrayString

ArrayString

Who am I?


What I write about


Recent Posts

Mysql 主从同步配置

Mysql 主从同步配置

环境

CentOS Linux release 7.6.1810 (Core)

主服务器IP:192.168.8.31

从服务器IP:192.168.8.32

保证主从服务器可以正常通信,能ping通即可

主服务器配置:

1.备份主服务器的数据

在终端执行如下命令:

mysqldump -u用户名 -p密码 --all-databases --lock-all-tables > ~/master_db.sql

将来会在家目录下生产一个master_db.sql的数据库文件,即备份文件

2.编辑设置mysqld的配置文件

设置log_bin和server-id 在终端执行如下命令:

# sudo vim /etc/my.cnf #配置文件位置改成自己的

...
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# bind-address = 127.0.0.1 #注释掉,允许其他地址连接
log_bin = mysql-bin
expire_logs_days = 1
server-id = 1
max_binlog_size = 100m
...

修改完毕之后保存退出,并且把MySQL服务器进行重启

# systemctl restart mysqld.service

3.登入主服务器Ubuntu中的mysql,创建用于从服务器同步数据使用的帐号:

终端输入如下指令连接MySQL:

# mysql –u用户名 –p密码

mysql> GRANT REPLICATION SLAVE ON *.* TO'slave'@'%' identified by 'slave'; #创建用于从服务器同步数据使用的帐号

mysql> FLUSHPRIVILEGES; #刷新

4.获取主服务器的二进制日志信息

在MySQL服务器上输入如下命令来获取二进制日志信息,记录下二进制文件的名字以及position位置信息,下面从服务器配置要用到

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.002569 | 62908919 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5.复制master_db.sql 到从服务器

#scp ~/master_db.sql root@192.168.8.32:~/
Password:

从服务器配置:

1. 导入主数据库

# mysql -u用户名-p密码 < ~/master_db.sql

2.修改从服务器的配置文件my.cnf

...
[mysqld]
server-id = 2 # 不能和主库一样
...

保持退出之后重启从服务器

# systemctl restart mysqld.service

3.连接到从服务器,进行同步工作

mysql>change master to master_host='192.168.8.31', master_user='slave', master_password='slave',master_log_file='mysql-bin.002569', master_log_pos=62908919;
mysql>start slave;

注意:第一个ip地址改成自己主服务器的ip地址,第二个二进制文件要跟主服务器的一致,第三个是pos位置也要与主服务器一致

4.查看同步状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.8.32
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002569
          Read_Master_Log_Pos: 87014571
               Relay_Log_File: mysqld-relay-bin.000021
                Relay_Log_Pos: 87014078
        Relay_Master_Log_File: mysql-bin.002569
             Slave_IO_Running: Yes #Yes 表示正常
            Slave_SQL_Running: Yes #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: 87013915
              Relay_Log_Space: 87014955
              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 #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
                  Master_UUID: 5bc6026b-1f02-11e9-ad10-d05099cb9007
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           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)

测试同步 在主服务器创建一个进行增删改,查看从库是否同步成功。