设置主从服务器

最新推荐文章于 2026-01-07 17:44:51 发布

最新推荐文章于 2026-01-07 17:44:51 发布 · 173 阅读

文章标签：

#数据库

数据库专栏收录该内容

21 篇文章

订阅专栏

本文详细介绍了如何在MySQL环境中实现主从数据库复制，包括创建同步账号、配置从机、指定同步位置以及验证同步过程。通过实际操作演示，确保数据能够准确、实时地在主从服务器间同步。

测试环境

写道

A:192.168.0.219(master)
B:192.168.0.8(slave)

测试数据库:test-xf-2

创建同步帐号

写道

1.在master机上为slave机创建一个同步帐号;
grant replication slave on *.* to 'replicate'@'192.168.0.8' identified by '123456';

去192.168.0.8去测试一下能不能登陆.......mysql -h192.168.0.219 -ureplicate -p123456..............ok

先前Ａ机已经做好了master设置,此处略过....

mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000031
Position: 107
Binlog_Do_DB: test-xf-2
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)

ERROR:
No query specified

配置slave机

从机不需要再设置同步帐号了.

修改my.cnf

server-id       = 11
log-bin         = mysql-bin
replicate-do-db  = test-xf-2
replicate-ignore-db = mysql,information_schema

然后从启mysql服务..
$ sudo /usr/local/etc/rc.d/mysql-server restart
Stopping mysql.
Waiting for PIDS: 71739.
Starting mysql.

用change master语句指定同步位置..

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to 
    -> master_host = '192.168.0.219',
    -> master_user = 'replicate',
    -> master_password = '123456',
    -> master_log_file = 'mysql-bin.000031',
    -> master_log_pos  = 107;
Query OK, 0 rows affected (0.00 sec)

mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.219
                  Master_User: replicate
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000031
          Read_Master_Log_Pos: 107
               Relay_Log_File: queen-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000031
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test-xf-2
          Replicate_Ignore_DB: mysql,information_schema
           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: 107
              Relay_Log_Space: 409
              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)

ERROR: 
No query specified


Slave_IO_Running: Yes
Slave_SQL_Running: Yes
就没问题了;

测试

写道

往master(192.168.0.219)中插入数据看是否同步到slave.

我往master中插入'00000000',进入到.0.8的test-xf-2中发现也已经同步.....................OK