最近单位在用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已经成功
阅读(1374) | 评论(0) | 转发(0) |