Mysql5.1.70-主从环境搭建说明
1、简单声明
Mysql数据库部署在两个Windows节点上，节点规划如下：
-
192.168.58.1主库 -
192.168.58.163从库
数据库版本为：
Server version: 5.1.70-community MySQL Community Server (GPL)
Windows上的Mysql数据库 my.ini 配置文件一般在 C:\Program Files\MySQL\MySQL Server 5.1 目录下。
2、主库配置
①、 my.ini配置
# Master Slave
server_id = 1 #本机数据库ID标示为主，可以取IP地址第四部分
log_bin = master-binlog #启动二进制日志系统
sync_binlog = 1
binlog_format = row
log_slave_updates = 1
binlog_cache_size = 256M
max_binlog_cache_size = 2G
max_binlog_size = 1G
#auto_increment_increment=2 #表示自增长字段从那个数开始
#auto_increment_offset=1 #表示自增长字段从那个数开始
skip_slave_start=1
binlog_do_db=paradb,hisdb #二进制需要同步的数据库名
binlog_ignore_db=mysql #二进制不需要同步的数据库名
不指定同步库
如果不指定特定同步数据库，则不需要添加 binlog_do_db 参数。
# Master Slave
server_id = 1 #本机数据库ID标示为主，可以取IP地址第四部分
log_bin = master-binlog #启动二进制日志系统
sync_binlog = 1
binlog_format = row
log_slave_updates = 1
binlog_cache_size = 256M
max_binlog_cache_size = 2G
max_binlog_size = 1G
skip_slave_start=1
指定同步库
如果不指定特定同步数据库，则需要添加 binlog_do_db 参数。
# Master Slave
server_id = 1 #本机数据库ID标示为主，可以取IP地址第四部分
log_bin = master-binlog #启动二进制日志系统
sync_binlog = 1
binlog_format = row
log_slave_updates = 1
binlog_cache_size = 256M
max_binlog_cache_size = 2G
max_binlog_size = 1G
skip_slave_start=1
binlog_do_db=firstdb,seconddb #二进制需要同步的数据库名
binlog_ignore_db=mysql #二进制不需要同步的数据库名
②、 重启mysql服务
重启命令如下：
net stop mysql
net start mysql
重启效果如下：
C:\Windows\system32>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。
C:\Windows\system32>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。
③、 查看主库状态
- 不指定同步库
C:\Windows\system32>mysql -uroot -proot123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.70-community-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql>
mysql> show master status;
+----------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| master-binlog.000009 | 106 | | |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
备注： 也可以执行如下命令查询：
mysql>
mysql> show master status\G
*************************** 1. row ***************************
File: master-binlog.000010
Position: 411
Binlog_Do_DB: paradb,hisdb
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
mysql>
- 指定同步库
C:\Windows\system32>mysql -uroot -proot123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.70-community-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show master status;
+----------------------+----------+------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+------------------+------------------+
| master-binlog.000010 | 106 | firstdb,seconddb | mysql |
+----------------------+----------+------------------+------------------+
1 row in set (0.00 sec)
mysql>
④、 创建同步用户并授权
创建从库同步用户，并授权。
如创建一个名叫 syncuser的用户，密码为 snycuser ：
create user syncuser@'%' identified by 'syncuser';
grant replication slave,replication client on *.* to 'syncuser'@'%';
flush privileges;
执行效果如下：
mysql> create user syncuser@'%' identified by 'syncuser';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave,replication client on *.* to 'syncuser'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql>
3、从库配置
①、 my.ini配置
# Master Slave
server_id = 168 #本机数据库ID标示为主，可以取IP地址第四部分
relay_log = relay-log #开启中继日志
②、 重启mysql服务
C:\Users\Administrator>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。
C:\Users\Administrator>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。
C:\Users\Administrator>
③、 指定主库
从库 mysql 执行如下 SQL语句。
change master to
master_host='192.168.58.1',
master_port=3306,
master_user='syncuser',
master_password='syncuser',
master_log_file='master-binlog.000010',
master_log_pos=106;
执行效果如下：
C:\Users\Administrator>mysql -uroot -proot123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.70-community MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> change master to
-> master_host='192.168.58.1',
-> master_port=3306,
-> master_user='syncuser',
-> master_password='syncuser',
-> master_log_file='master-binlog.000010',
-> master_log_pos=106;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
mysql>
mysql> reset slave; #第一次报错，执行一下这个语句就可以了。
Query OK, 0 rows affected (0.01 sec)
mysql> change master to
-> master_host='192.168.58.1',
-> master_port=3306,
-> master_user='syncuser',
-> master_password='syncuser',
-> master_log_file='master-binlog.000010',
-> master_log_pos=106;
Query OK, 0 rows affected (0.00 sec)
mysql>
④、 从库启动slave服务
开启 slave 服务，命令如下：
start slave
执行效果如下：
C:\Users\Administrator>mysql -uroot -proot123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.70-community MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql>
⑤、 从库状态查看
通过 show slave status\G 查看从库状态。
mysql>
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.58.1
Master_User: syncuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-binlog.000010
Read_Master_Log_Pos: 411
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 560
Relay_Master_Log_File: master-binlog.000010
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: 411
Relay_Log_Space: 709
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:
1 row in set (0.00 sec)
mysql>
4、案例
①、 主库配置
A、 创建database
创建名称为 demodb的数据库，字符集选择 gbk :
create database demodb character set gbk collate gbk_chinese_ci;
效果如下：
mysql>
mysql> create database demodb character set gbk collate gbk_chinese_ci;
Query OK, 1 row affected (0.00 sec)
mysql>
B、 my.ini 配置
# Master Slave
server_id = 1
log_bin = master-binlog
sync_binlog = 1
binlog_format = row
log_slave_updates = 1
binlog_cache_size = 256M
max_binlog_cache_size = 2G
max_binlog_size = 1G
skip_slave_start=1
binlog_do_db=demodb
binlog_ignore_db=mysql
C、 重启mysql服务
C:\Windows\system32>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。
C:\Windows\system32>
C:\Windows\system32>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。
D、 查看主库状态
C:\Windows\system32>mysql -uroot -proot123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.70-community-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show master status;
+----------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| master-binlog.000011 | 106 | demodb | mysql |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
mysql> show master status\G
*************************** 1. row ***************************
File: master-binlog.000011
Position: 106
Binlog_Do_DB: demodb
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
mysql>
E、 创建同步账号
参考《 创建同步用户并授权》章节内容。
②、 从库配置
A、 创建database
创建名称为 demodb的数据库，字符集选择 gbk :
create database demodb character set gbk collate gbk_chinese_ci;
效果如下：
mysql>
mysql> create database demodb character set gbk collate gbk_chinese_ci;
Query OK, 1 row affected (0.00 sec)
mysql>
注意事项： 当然，主库 database (含建表脚本) 创建完后可以导出来，然后重新导入到从库也可以。这样的话，从库不用重新创建 database及建表脚本！！！
B、my.ini配置
# Master Slave
server_id = 168 #本机数据库ID标示为主，可以取IP地址第四部分
relay_log = relay-log #开启中继日志
C、 重启mysql服务
C:\Users\Administrator>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。
C:\Users\Administrator>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。
D、 指定主库
从库 mysql 执行如下 SQL语句。
change master to
master_host='192.168.58.1',
master_port=3306,
master_user='syncuser',
master_password='syncuser',
master_log_file='master-binlog.000011',
master_log_pos=106;
执行效果如下：
C:\Users\Administrator>mysql -uroot -proot123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.70-community MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> stop slave; #1. 先停止slave服务
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> reset slave; #2. 重置slave配置
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> change master to
-> master_host='192.168.58.1',
-> master_port=3306,
-> master_user='syncuser',
-> master_password='syncuser',
-> master_log_file='master-binlog.000011',
-> master_log_pos=106;
Query OK, 0 rows affected (0.00 sec)
mysql>
E、从库slave启动
mysql>
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.58.1
Master_User: syncuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-binlog.000011
Read_Master_Log_Pos: 106
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 255
Relay_Master_Log_File: master-binlog.000011
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: 106
Relay_Log_Space: 404
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:
1 row in set (0.00 sec)
mysql>
③、 数据同步验证
A、主库创建表
use demodb;
/* ======== Creating table Student ========;*/
drop table if exists Student;
create table Student
(
Name char(24) NOT NULL,
Age int(5) NULL,
Class char(24) NULL,
primary key(Name)
);
grant select on Student to dbuser,syncuser;
执行效果如下：
mysql>
mysql> use demodb;
Database changed
mysql> /* ======== Creating table Student ========;*/
mysql> drop table if exists Student;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> create table Student
-> (
-> Name char(24) NOT NULL,
-> Age int(5) NULL,
-> Class char(24) NULL,
-> primary key(Name)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> grant select on Student to dbuser,syncuser;
Query OK, 0 rows affected (0.01 sec)
mysql>
B、主库插入记录
INSERT INTO Student (Name,Age,Class) VALUES ('zhangsan',20,'高三');
INSERT INTO Student (Name,Age,Class) VALUES ('lisi',22,'高三');
INSERT INTO Student (Name,Age,Class) VALUES ('wangwu',19,'高二');
commit;
插入效果如下：
mysql>
mysql> INSERT INTO Student (Name,Age,Class) VALUES ('zhangsan',20,'高三');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO Student (Name,Age,Class) VALUES ('lisi',22,'高三');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO Student (Name,Age,Class) VALUES ('wangwu',19,'高二');
Query OK, 1 row affected (0.02 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from Student;
+----------+------+-------+
| Name | Age | Class |
+----------+------+-------+
| lisi | 22 | 高三 |
| wangwu | 19 | 高二 |
| zhangsan | 20 | 高三 |
+----------+------+-------+
3 rows in set (0.00 sec)
mysql>
C、从库同步查询
mysql> use demodb;
Database changed
mysql>
mysql>
mysql>
mysql>
mysql> show tables;
+------------------+
| Tables_in_demodb |
+------------------+
| student |
+------------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> select * from Student;
+----------+------+-------+
| Name | Age | Class |
+----------+------+-------+
| lisi | 22 | 高三 |
| wangwu | 19 | 高二 |
| zhangsan | 20 | 高三 |
+----------+------+-------+
1 row in set (0.00 sec)
mysql>
④、 注意事项
A、主库从库均创建database
主库创建 database 后，可以将 database 导入到从库。
也可以主库从库同步创建 database 及其 表。
B、 从库指定主库
从库指定主库时 master_log_file 和 master_log_pos 一定要匹配，否则会出现从库同步失败问题。
master_log_file='master-binlog.000011',
master_log_pos=106;
附录1、Mysql命令行\g和\G 和;
"\G"、"\g" 与 ";" 差异的地方只是 垂直显示 。
mysql>
mysql> use demodb;
Database changed
mysql>
mysql> select * from StudentA;
+----------+------+-------+
| Name | Age | Class |
+----------+------+-------+
| lisi | 22 | 高三 |
| wangwu | 19 | 高二 |
| zhangsan | 20 | 高三 |
+----------+------+-------+
3 rows in set (0.00 sec)
mysql>
mysql> select * from StudentA\g
+----------+------+-------+
| Name | Age | Class |
+----------+------+-------+
| lisi | 22 | 高三 |
| wangwu | 19 | 高二 |
| zhangsan | 20 | 高三 |
+----------+------+-------+
3 rows in set (0.00 sec)
mysql>
mysql> select * from StudentA\G
*************************** 1. row ***************************
Name: lisi
Age: 22
Class: 高三
*************************** 2. row ***************************
Name: wangwu
Age: 19
Class: 高二
*************************** 3. row ***************************
Name: zhangsan
Age: 20
Class: 高三
3 rows in set (0.00 sec)
mysql>
附录2、Mysql导入导出数据库(database)
①、 database数据库导出
命令格式：
mysqldump -u[username] -p[password] 数据库名 > output.dmp
示例：
C:\Windows\system32>
C:\Windows\system32>mysqldump -uroot -proot123 demodb > C:\Users\kalami\Desktop\mysql\backup\demodb.dmp
C:\Windows\system32>
②、 database数据库导入
先创建 ”新的数据库“【database】，然后导入备份。
create database newdemodb character set gbk collate gbk_chinese_ci;
命令格式：
mysql -u[username] -p[password] 新数据库名 < output.dmp
示例：
C:\Users\kalami\Desktop\mysql\backup>mysql -uroot -proot123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.70-community-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> create database newdemodb character set gbk collate gbk_chinese_ci; #1.创建新数据库【database】
Query OK, 1 row affected (0.00 sec)
mysql> quit
Bye
C:\Users\kalami\Desktop\mysql\backup>mysql -uroot -proot123 newdemodb < demodb.dmp #2.导入数据备份
C:\Users\kalami\Desktop\mysql\backup>
C:\Users\kalami\Desktop\mysql\backup>mysql -uroot -proot123 #3.重新连接数据库
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.1.70-community-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> use newdemodb; #4.验证database
Database changed
mysql> show tables;
+---------------------+
| Tables_in_newdemodb |
+---------------------+
| student |
| studenta |
+---------------------+
2 rows in set (0.00 sec)
mysql> select * from studenta;
+----------+------+-------+
| Name | Age | Class |
+----------+------+-------+
| lisi | 22 | 高三 |
| wangwu | 19 | 高二 |
| zhangsan | 20 | 高三 |
+----------+------+-------+
3 rows in set (0.00 sec)
mysql>
1023
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
