mysql 第20章复制

2015-10-24

环境介绍
主机名        IP                用途
mdb         10.10.2.87        Master
sdb1        10.10.2.88        Slave


Master
//配置文件
[root@mdb ~]# vim /etc/my.cnf
[mysqld]
server_id = 87
log_bin = /data/mysql/3306/mysql-bin

//重启mysql数据库
[root@mdb ~]# service mysqld restart

//创建测试库、表、数据
[root@mdb ~]# mysql -uroot -ppro#pateo
mysql> create database rep_db1;
mysql> use rep_db1;
mysql> create table test1(id int auto_increment not null primary key,name varchar(20));
mysql> insert into rep_db1.test1(name) values('db1_test1'),('db1_test2'),('db1_test3'),('db1_test4');
mysql> create table test2(id int auto_increment not null primary key,name varchar(20));
mysql> insert into rep_db1.test2(name) values('db1_test5'),('db1_test6'),('db1_test7'),('db1_test8');

mysql> create database rep_db2;
mysql> use rep_db2;
mysql> create table test2(id int auto_increment not null primary key,name varchar(20));
mysql> insert into test2(name) values('db2_test5'),('db2_test6'),('db2_test7'),('db2_test8');


//创建复制帐号
[root@mdb ~]# mysql -uroot -ppro#pateo
mysql> grant replication slave,replication client on *.* to rep@'10.10.2.88' identified by 'rep';

//查看master状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


Slave
//配置文件
[root@sdb1 ~]# vim /etc/my.cnf
[mysqld]
server_id = 88
master_host=10.10.2.87
master_user=rep
master_password=rep
master_port=3306
#指定复制的库
replicate-do-db = rep_db1
#指定复制的表
replicate-do-table = rep_db1.test1

//重启mysql数据库
[root@mdb ~]# service mysqld restart

//建立主从连接
[root@mdb ~]# mysql -uroot -ppro#pateo
mysql> change master to master_host='10.10.2.87',master_port=3306,master_user='rep',master_password='rep',master_log_file='mysql-bin.000004',master_log_pos=214;

//启动slave进程
mysql> start slave;

//查看slave状态
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.2.87
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 1899
               Relay_Log_File: sdb1-relay-bin.000012
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: rep_db1
          Replicate_Ignore_DB: 
           Replicate_Do_Table: rep_db1.test1
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1899
              Relay_Log_Space: 455
              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: 87
                  Master_UUID: cd14da62-7ae3-11e5-aede-005056a7475f
             Master_Info_File: /data/mysql/3306/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)