ITPub博客

首页 > 数据库 > MySQL > mysql 主从复制搭建详细步骤

mysql 主从复制搭建详细步骤

原创 MySQL 作者:liqilin0429 时间:2015-04-24 14:07:16 0 删除 编辑




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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2010-08-13

  • 博文量
    100
  • 访问量
    203301