欢迎投稿

今日深度:

MySQL主从复制,mysql主从

MySQL主从复制,mysql主从


本实验室配置一个全新的master和slave,最简单的方法去配置新的master和slave是从一个不同的数据库服务器生成一个数据库dump文件,然后导入到新的复制数据库的master里去,这些数据会自动的复制数据到slave数据库里去的。

 

实验环境:
master and the slave server machine have the same configuration as followings:
[root@master1 ~]# uname -a
Linux master1 2.6.18-8.el5 #1 SMP Fri Jan 26 14:15:21 EST 2007 i686 i686 i386 GNU/Linux

mysql> select @@version;
+---------------------------------------+
| @@version                             |
+---------------------------------------+
| 5.6.19-enterprise-commercial-advanced |
+---------------------------------------+
1 row in set (0.00 sec)

master IP: 192.168.92.11
slave  IP: 192.168.92.111


二,主从服务器分别作的事情
Master sever:
changes data
keeps log of changes

slave server:
ask master for events
executes events

三,复制的类型(同步|异步|半同步)
Synchronouse replication
1,data is replicated and appllied then committed
2,provides consistency ,but slower
3,provided by MySQL Cluster

Asynchronous replication
1,transactions committed immediately and replicated
2,no consistency,but faster
3,provided by MySQL Server

SemiSyncReplication
1,provided by Google

四,复制所需要的日志
Binary log的作用:
1,log every change (select 是不记录的,只记录改变的)
2,split into transaction groups

两个复制相关的二进制文件:
File: master_bin.NNNNNN
1,The actual contents of the binlog
File: master_bin.index
1,an index file over the files above


五,复制所用到的线程
Master: I/O thread
Slave: I/O thread and SQL Thread


master.info contains:
1,Read coordinates: which contains master log name and master log position
2,Connection information: which contains the following two information:
a,host,user,password ,port
b,SSL keys and certificates

relay-log.info contains:
1,Group master coordinates: which contains master log name and master log position
2,Group relay log coordinates: which contains relay log name and relay log position

 

六,具体的实施步骤:
The following 8 Steps are need to be taken to configure the master slave replication:
1,Fix my.cnf file for master and slave
2,add user and grants on master
3,make sure the related configuration
4,on the master,use the SHOW MASTER STATUS statement to determine the current binary log file name and position:
5,load backup dump file into master
6,configure slave
7,start slave
8,check slave status  show slave status\G


1,Fix my.cnf file for master and slave
Master configuration --required(必选择)
log_bin = master_bin
server_id =11

配置好了后,重新启动mysql服务
[root@master1 ~]# cd /etc/rc.d/init.d/
[root@master1 init.d]# service mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.........                                    [  OK  ]


slave configuration --required(必选择)
server_id=111   The master and slave must have the different server_id
配置好了后,重新启动mysql服务
[root@slave1 init.d]# service mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.........                                    [  OK  ]


2,add user and grants on master
mysql> CREATE USER
'repl'@'192.168.92.111' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO
'repl'@'192.168.92.111';   


3,make sure the related configuration
show variables like 'server%';
show variables like 'log%';
show grants for
'repl'@'192.168.92.111';


mysql> show variables like 'server%';
+----------------+--------------------------------------+
| Variable_name  | Value                                |
+----------------+--------------------------------------+
| server_id      | 11                                   |
| server_id_bits | 32                                   |
| server_uuid    | 303c6931-0d5e-11e4-9f5c-000c29f09a2c |
+----------------+--------------------------------------+
3 rows in set (0.00 sec)

show variables like 'log%';  看log_bin是否开启用
mysql> show variables like 'log%';
+----------------------------------------+---------------------------------+
| Variable_name                          | Value                           |
+----------------------------------------+---------------------------------+
| log_bin                                | ON                              |
| log_bin_basename                       | /var/lib/mysql/master_bin       |
| log_bin_index                          | /var/lib/mysql/master_bin.index |
| log_bin_trust_function_creators        | OFF                             |
| log_bin_use_v1_row_events              | OFF                             |
| log_error                              | /var/lib/mysql/master1.err      |
| log_output                             | FILE                            |
| log_queries_not_using_indexes          | OFF                             |
| log_slave_updates                      | OFF                             |
| log_slow_admin_statements              | OFF                             |
| log_slow_slave_statements              | OFF                             |
| log_throttle_queries_not_using_indexes | 0                               |
| log_warnings                           | 1                               |
+----------------------------------------+---------------------------------+
13 rows in set (0.00 sec)


mysql> show grants for 'repl'@'192.168.92.111';
+------------------------------------------------------------------------------------------------------------------------------+
| Grants for
repl@192.168.92.111                                                                                               |
+------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO
'repl'@'192.168.92.111' IDENTIFIED BY PASSWORD '*809534247D21AC735802078139D8A854F45C31F3' |
+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

4,on the master,use the SHOW MASTER STATUS statement to determine the current binary log file name and position:
mysql> show master status\G
*************************** 1. row ***************************
             File: master_bin.000001
         Position: 589
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

 


5,load backup dump file into master
一定要先创建一个数据库
mysql> create database cddl;
Query OK, 1 row affected (0.02 sec)


还原数据库到master上:
mysql -h 192.168.92.11 -uroot -ppassword cddl< /mysql_installer/cddl20140702.sql


6,configure slave
CHANGE MASTER TO
MASTER_HOST='192.168.92.11',
MASTER_USER='repl',
MASTER_PASSWORD='slavepass',
MASTER_PORT=3306,
MASTER_LOG_FILE='master_bin.000001',
MASTER_LOG_POS=589,
MASTER_CONNECT_RETRY=10;


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

 

8,check slave status 
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.92.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: master_bin.000002
          Read_Master_Log_Pos: 120
               Relay_Log_File: slave1-relay-bin.000002
                Relay_Log_Pos: 43341241
        Relay_Master_Log_File: master_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: 43341546
              Relay_Log_Space: 46042813
              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: 62237
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: 11
                  Master_UUID: 24f8486c-0d8c-11e4-a088-000c29f09a2c
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: creating table
           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.01 sec)

ERROR:
No query specified


从上面可以看出备库正在做复制。

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.92.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: master_bin.000002
          Read_Master_Log_Pos: 120
               Relay_Log_File: slave1-relay-bin.000003
                Relay_Log_Pos: 284
        Relay_Master_Log_File: master_bin.000002
             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: 46042639
              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: 11
                  Master_UUID: 24f8486c-0d8c-11e4-a088-000c29f09a2c
             Master_Info_File: /var/lib/mysql/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)

ERROR:
No query specified

从上面可以看出replicate完成,至此最简单的 master - slave配置成功。

 

 

下面测试一下主从复制:

master:

mysql> create table TT(id int ,name varchar(30));
Query OK, 0 rows affected (0.10 sec)

mysql> insert into TT VALUES (1,'FAFAFAFAFA');
Query OK, 1 row affected (0.09 sec)

mysql> insert into TT VALUES (1,'FAFAFAFAFA2');
Query OK, 1 row affected (0.04 sec)

mysql> insert into TT VALUES (3,'FAFAFAFAFA3');
Query OK, 1 row affected (0.00 sec)

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

 

在slave上查询:

mysql> select * from TT;
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | FAFAFAFAFA  |
|    1 | FAFAFAFAFA2 |
|    3 | FAFAFAFAFA3 |
+------+-------------+
3 rows in set (0.02 sec)
可以看出顺利的传到slave 库上来了。

 


Mysql主从复制的问题

--主机开两个窗口,一个进入mysql,一个是shell
--主机阻断写操作
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 983407 | openser | mysql,test |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
--另外一个窗口导出主机数据库
mysqldump -u root -ppassword --master-data=2 --opt -R openser > openser20120204.sql
--刚才的窗口主机解锁
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql>
--打包数据文件到从机
drop database openser;
create database openser;
mysql -u root -ppassword openser < openser20120204.sql
--从机操作
SLAVE STOP;
reset slave;
CHANGE MASTER TO MASTER_HOST='192.168.21.26',
MASTER_USER='repl_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=983407;
start slave;
show slave status\G
 

mysql的主从复制,linux重启后遇到问题

主机的mysql重启,但是你的从机mysql肯定是没重启才出现binlog索引不一致的现象,我认为是,从机mysql在主机重启之前slave_io_running线程始终保持和主机通信,传输binlog的更新。当主机挂掉之后,slave_io_running一直等待,状态变成NO,整个复制就会停掉,应该说是处于sleep状态,后来主机恢复,从机是没法辨别的,只能stop slave 然后再start,或者change master操作下,实在不行,只能重启从机的mysql了
 

www.htsjk.Com true http://www.htsjk.com/shujukunews/2188.html NewsArticle MySQL主从复制,mysql主从 本实验室配置一个全新的master和slave,最简单的方法去配置新的master和slave是从一个不同的数据库服务器生成一个数据库dump文件,然后导入到新的复制数据库的m...
评论暂时关闭