mysql 主从复制搭建详细步骤

mysql 主从复制搭建详细步骤

主机名称	IP地址	端口号	版本信息	操作系统
Master	192.168.111.21	3306	5.6.17	Redhat5.4
Slave	192.168.111.22	3306	5.6.23	Redhat5.4

配置成功的前置条件

版本一致或是从服务器的版本要高于主服务

初始化表，并在后台启动mysql

主从服务器安装并启动成功

修改root的密码

主服务器上授权（master）

修改主机服务的配置文件（/etc/my.cnf）

添加如下的内容

[root@master etc]# cat my.cnf

[mysqld]

log-bin=mysqsl-bin

server-id=101

修改后必须重新启动被修改的服务器才可以生效

mysql>
show master status;

Empty
set (0.00 sec)

如果还是空，就查看二进制日志是否开启

mysql>
show variables like '%log_bin%';

+---------------------------------+-------+

|
Variable_name                   | Value |

+---------------------------------+-------+

|
log_bin                         |
OFF   |

|
log_bin_basename                |       |

|
log_bin_index                   |       |

|
log_bin_trust_function_creators | OFF   |

|
log_bin_use_v1_row_events       |
OFF   |

|
sql_log_bin                     | ON    |

+---------------------------------+-------+

6
rows in set (0.00 sec)

那说明二进制日志没有开启，因此对配置文件查看，尤其查看配置文件的名称my.cnf（我写成了my.conf），所以没有配置上去。

授权给从数据库服务器（slave server）

[root@master ~]# pwd

/root

mysql> grant replication slave on *.* to
'replication'@'192.168.111.22' identified by 'qilin';

Query OK, 0 rows affected (0.07 sec)

查询主数据库状态

mysql> show master status;

+-------------------+----------+--------------+------------------+-------------------+

| File              | Position | Binlog_Do_DB |
Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------------+----------+--------------+------------------+-------------------+

| mysqsl-bin.000001 |      120 |              |                  |                   |

+-------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

 从服务器上的配置（slave）

修改从服务器的配置文件（/etc/my.cnf)

# add mian by liqilin

log-bin           = mysql-bin

server-id         = 201

relay-log         = mysql-relay-bin

log-slave-updates
 = 1

read-only         = 1

从新启动服务器，启动MYSQL

执行同步SQL语句

mysql> change master to

   
-> master_host='192.168.111.21',

   
-> master_user='replication',

   
-> master_password='qilin',

   
-> master_log_file='mysqsl-bin.000001',

   
-> master_log_pos=120;

Query OK, 0 rows affected, 2 warnings (0.04
sec)

主从同步检查

mysql> show slave status\G

*************************** 1. row
***************************

               Slave_IO_State:

                  Master_Host: 192.168.111.21

                  Master_User: replication

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File:
mysqsl-bin.000001

         
Read_Master_Log_Pos: 120

               Relay_Log_File:
mysql-relay-bin.000001

                Relay_Log_Pos: 4

       
Relay_Master_Log_File: mysqsl-bin.000001

             Slave_IO_Running: No

           
Slave_SQL_Running: No

              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: 120

              Relay_Log_Space: 120

              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: NULL

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: 0

                  Master_UUID:

            
Master_Info_File: /usr/local/mysql/data/master.info

                    SQL_Delay: 0

         
SQL_Remaining_Delay: NULL

     
Slave_SQL_Running_State:

          
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)

mysql>

启动Slave同步进程

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

再次查看状态

mysql> show slave status\G

*************************** 1. row
***************************

            
  Slave_IO_State: Waiting for master to send
event

                  Master_Host: 192.168.111.21

                  Master_User: replication

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File:
mysqsl-bin.000001

         
Read_Master_Log_Pos: 120

               Relay_Log_File:
mysql-relay-bin.000002

                Relay_Log_Pos: 284

       
Relay_Master_Log_File: mysqsl-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: 120

              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: 101

                  Master_UUID:
75fc94ca-e819-11e4-b1ac-000c2969fa00

            
Master_Info_File: /usr/local/mysql/data/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)

mysql>

变化了项目

Seconds_Behind_Master

Slave_SQL_Running_State

I/O线程创建的连接(SLAVE)

mysql> show processlist \G

*************************** 1. row
***************************

    
Id: 3

  
User: root

  
Host: localhost

    
db: NULL

Command: Query

  
Time: 0

 
State: init

  
Info: show processlist

*************************** 2. row
***************************

    
Id: 4

  
User: system user

  
Host:

    
db: NULL

Command: Connect

  
Time: 443

 
State: Waiting for master to send event

  
Info: NULL

*************************** 3. row
***************************

    
Id: 5

  
User: system user

  
Host:

    
db: NULL

Command: Connect

  
Time: 443

 
State: Slave has read all relay log; waiting for the slave I/O thread to
update it

  
Info: NULL

3 rows in set (0.00 sec)

I/O线程创建的连接(master)

mysql> show processlist \G

*************************** 1. row
***************************

    
Id: 1

  
User: root

  
Host: localhost

    
db: NULL

Command: Query

  
Time: 0

 
State: init

  
Info: show processlist

*************************** 2. row
***************************

    
Id: 2

  
User: replication

  
Host: 192.168.111.22:37333

    
db: NULL

Command: Binlog Dump

  
Time: 377

 
State: Master has sent all binlog to slave; waiting for binlog to be
updated

  
Info: NULL

2 rows in set (0.03 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20976446/viewspace-1595778/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20976446/viewspace-1595778/