mysql replication---master/salve IN ONE PC

最近单位在用DB2 Q-replication，因此先拿mysql的master/slave进行安装测试，具体的步骤如下：

MYSQL version

C:\Program Files\MariaDB 5.5\bin>mysql --version
mysql Ver 15.1 Distrib 5.5.25-MariaDB, for Win32 (x86)

以下为在一天电脑上面的实施步骤：

1 instal master  mysql  instance 

2 install  slave mysql instance 

method： 

copy  installed file C:\Program Files\MariaDB 5.5 to a new disk :E:\slave

3  install service  of slave  instance 

E:\slave\bin>mysqld.exe --install slave --default-flie=e:\salve\my.ini
Service successfully installed.

4 config slave file file 

C:\Program Files\MariaDB 5.5\data、my.ini

[mysqld]
datadir=C:/Program Files/MariaDB 5.5/data
port=3306
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
default_storage_engine=innodb
innodb_buffer_pool_size=396M
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size=50M
server-id =1
log-bin=mysql-bin
[client]
port=3306

E:\slave\data\my.ini

[mysqld]
datadir=e:/slave/data
port=3309
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
default_storage_engine=innodb
innodb_buffer_pool_size=396M
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size=50M
server-id =127
[client]
port=3309

5 start master,slave services

E:\slave\bin>net start slave
slave 服务正在启动 ..
slave 服务已经启动成功。


E:\slave\bin>net start mysql
MySQL 服务正在启动 ..
MySQL 服务已经启动成功。

6 在master上创建一个slave账户以用来执行replication

C:\Program Files\MariaDB 5.5\bin>mysql -uroot -p
Enter password: *********
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.25-MariaDB mariadb.org binary distribution

This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> \u mysql
Database changed
MariaDB [mysql]> grant replication slave on *.* to 'slaveuser'@localhost identi
fied by 'xxxxxx'
    -> ;
Query OK, 0 rows affected (0.04 sec)

6 在复制之前，确认master instance开启了如下参数

serverid =1 

log-bin=mysql-bin

MariaDB [(none)]> show variables like 'ser%' \G;
*************************** 1. row ***************************
Variable_name: server_id
        Value: 1
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'log%' \G;
*************************** 1. row ***************************
Variable_name: log
        Value: OFF
*************************** 2. row ***************************
Variable_name: log_bin
        Value: ON
*************************** 3. row ***************************

修该slave 库是使用相同的设置

MariaDB [(none)]> show variables like 'ser%' \G;
*************************** 1. row ***************************
Variable_name: server_id
        Value: 127
1 row in set (0.00 sec)

subscribe master database 

定制你要replication的DB，myblog

master首先设置读锁，读取日志文件名和偏移量，备份要同步的数据库，然后解锁

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| myblog |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)

做到这步卡住了，明天晚上不上，相对来说还是比较简单的，

E:\slave\bin>mysql -uroot -p  --database=myblog < dump.sql

Enter password: *********

ERROR at line 1478: Unknown command '\''.

备份文件在导入到新表的时候报错~~~~~~~~~~~~~~字符集不兼容，

解决方法：

E:\slave\bin>mysql -u root -p --default-character-set=utf8 slaveblog < dump.sql
Enter password: *********

8 使用命令开启复制过程

在执行该命令之前，首先确定master的状态：

MariaDB [(none)]> show master status \G;
*************************** 1. row ***************************
            File: mysql-bin.000002
        Position: 245
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

在slave上执行

change master to
 master_host='127.0.0.1',
 master_user='slaveuser',
 master_password='xxxxxxxx',
 master_log_file='mysql-bin.000002',
 master_log_pos=245,
 master_port=3306,
 master_connect_retry=10;

启动服务，请确定结果

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: slaveuser
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 245
               Relay_Log_File: kinfinger-PC-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-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: 245
              Relay_Log_Space: 830
              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: 1
1 row in set (0.00 sec)

ERROR: No query specified

进行验证测试： 

master上执行 :

MariaDB [myblog]> create table  rep( id int not null) engine=innodb;
Query OK, 0 rows affected (0.17 sec)

MariaDB [myblog]> insert into rep values(8);
Query OK, 1 row affected (0.00 sec)

SLAVE DO :

MariaDB [myblog]> select * from rep ;
+----+
| id |
+----+
|  8 |
+----+
1 row in set (0.00 sec)

有上可知，我们的replication已经成功