网站首页  汉语字词  英语词汇  考试资料  写作素材  旧版资料

请输入您要查询的考试资料：

　

标题 MySQL主从复制
内容
    实验环境：
    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 statusG
    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 IDENTIFIED BY 'slavepass';
    mysql> GRANT REPLICATION SLAVE ON *.* TO ;
    3,make sure the related configuration
    show variables like 'server%';
    show variables like 'log%';
    show grants for ;
    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 ;
    +------------------------------------------------------------------------------------------------------------------------------+
    | Grants for |
    +------------------------------------------------------------------------------------------------------------------------------+
    | GRANT REPLICATION SLAVE ON *.* TO 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 statusG
    *************************** 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 statusG;
    *************************** 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 statusG;
    *************************** 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 库上来了。
随便看

　

在线学习网考试资料包含高考、自考、专升本考试、人事考试、公务员考试、大学生村官考试、特岗教师招聘考试、事业单位招聘考试、企业人才招聘、银行招聘、教师招聘、农村信用社招聘、各类资格证书考试等各类考试资料。

　

Copyright © 2002-2024 cuapp.net All Rights Reserved
更新时间：2025/5/22 16:07:09