JIYIK CN >

I'm learning about MySQL master-slave replication recently and want to apply it to a project at work. Since I'm new to it, I don't understand it very well, so I can only share how to make a simple configuration.

At the beginning, we configure a master server and a slave server, and then add a slave server after using it for a period of time.

Configuration of the master server

First we configure the main server.

1. Open the MySQL configuration file my.cnf and configure the following two options in the configuration file

[mysqld]
log-bin = mysql-bin
server-id = 1 //Note the server-id here. In a group of master and slave servers, each server (whether it is a master server or a slave server) has an id, and this id is unique and cannot be the same between servers. If this option is not configured, the server-id of the master server defaults to 0, so no slave server is allowed to connect

2. Create a user in the master server for the slave server connection

mysql> CREATE USER 'repluser'@'%' IDENTIFIED BY 'repluser'
//The first repluser represents the account name and the second repluser represents the password. Of course, you can define these yourself. % means accepting connections from any host
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%'

3. Get the binlog information of the primary server

First, refresh the write status of all tables and blocks

mysql> FLUSH TABLES WITH READ LOCK

Then display the master's binlog status

mysql>SHOW MASTER STATUS

For a new server, the results are as follows

+-------------------+----------------+------------------+------------------+------------------+ |
File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

Here you need to remember the values ​​of the file and position fields. These two options are needed when configuring the slave server.

4. Release the read lock

mysql> UNLOCK TABLES

At this point, the configuration of the master server is complete. It is really simple. Next, configure the slave server

 Slave server configuration

After configuring the master server, we will start configuring the slave server.

1. First, you should specify a server-id for the slave server in the configuration file my.cnf and turn off the binlog log of the slave server.

[mysqld]
#log-bin = mysql-bin
server-id = 2

2. Start the slave server

mysql>START SLAVE

3. This step is the most important. We need to tell the slave server the necessary information to connect to the master server so that it can establish communication with the master server.

mysql> CHANGE MASTER TO
           -> MASTER_HOST = 'master service address',
           -> MASTER_USER = 'repluser', //This is the new user created above-
           > MASTER_PASSWORD = 'preluser',
           -> MASTER_LOG_FILE = 'mysql-bin.000001', //This is the value of the file that needs to be remembered in the third step above-
           > MASTER_LOG_POS = 13; //This is the value of the position that needs to be remembered in the third step above

4. Then check the status of the slave server

mysql> SHOW SLAVE STATUS\G

The results displayed are as follows

Slave_IO_State:  Waiting for master to send event //等待主服务器的操作
       Master_Host:  192.168.144.128   //这是我主服务器的地址
       Master_User:  repuser  //用户名
        Master_Port:  3306
      Connect_Retry: 60
     Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 13
       Relay_Log_File: localhost-relay-bin.000007
       Relay_Log_Pos: 23
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: 336
      Relay_Log_Space: 642
        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   //主服务器的id

出现以上信息说明从服务器配置成功。

当然我们一开始就可以按照上述步骤配置多个从服务器，只是server-id不同。

主服务器和从服务器都配置完成以后我们可以做一个测试，在主服务器中的test数据库中创建一个表，并插入一些数据

mysql> use test;
mysql> create table repl(id int(5) not null primary key auto_increment,name varchar(100));
mysql> insert into repl(name)values(‘test1’);
mysql> insert into repl(name)values(‘test2’);

然后在从服务器中查看数据是否同步，没特殊情况的话数据是同步过来的。

在当前的主从复制集合中新加一台从服务器

对于已经存在的从服务器我们称之为 oldslave（其ip地址为192.168.144.131） ，新添加的称之为 newslave （其ip地址为192.168.144.132）。

1. 首先关闭已经存在的从服务器 oldslave

mysql> mysqladmin shutdown

2. 将oldslave的data目录拷贝到 newslave中

mysql]# tar zcvf data.tar.gz data
mysql]# scp data.tar.gz  root@192.168.144.132:mysql安装目录

3. 进入newslave服务器中

mysql]# tar –zxvf data.tar.gz  //解压传输过来的data包，解压完以后newslave中的data目录就和oldslave中的目录合并，如果oldslave中的data目录中没有master.info和relay-log.info两个文件的话那还需要将这两个文件拷贝过来

4. 修改newslave的server-id 并且关闭binlog 日志

[mysqld]
#log-bin = mysql-bin
server-id = 3

5. 在newslave上开启mysql服务，并且开启slave机制

mysql]# service mysqld start
mysql> START SLAVE;

这样如果没有特殊情况的话就成功的加入了一台从服务器。

以上所有只是一个简单的主从复制集合的配置，想更深入的了解还需参考官方的文档