mysql 互为主备的简单搭建
来源:互联网 发布:淘宝服务热线 人工 编辑:程序博客网 时间:2024/06/15 15:53
192.168.190.128《====》192.168.190.129
master----》slave
slave《----master
1.在master 128 上配置my.cnf文件,添加下列参数:
server-id=1
log-bin=mysql-bin
log-salve-updates
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
重新启动mysql
[root@calvin1 ~]# /etc/init.d/mysqld start
Starting MySQL: [ OK ]
[root@calvin1 ~]# mysql -u root -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.77-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| calvin |
| calvin2 |
| mysql |
| sampdb |
| test |
| testdb |
+--------------------+
7 rows in set (0.00 sec)
2.在master 129 上配置my.cnf文件,添加下列参数:
server-id=2
log-bin=mysql-bin
log-salve-updates
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2
重新启动mysql
[root@calvin2 ~]# /etc/init.d/mysqld start
Starting MySQL: [ OK ]
[root@calvin2 ~]# mysql -u root -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.77-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| calvin |
| calvin2 |
| mysql |
| sampdb |
| test |
| testdb |
+--------------------+
7 rows in set (0.00 sec)
3.在在master 128上创建复制账号:
mysql> grant replication slave,file on *.* to mysync@'192.168.190.129' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
对所有表添加只读锁:
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
查看master二进制文件,pos号:
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000004
Position: 906
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
4.在在master 129上创建复制账号:
mysql> grant replication slave,file on *.* to mysync@'192.168.190.128' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
对所有表添加只读锁:
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
查看master二进制文件,pos号:
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 318
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
5.修改同步参数:
根据第3步读取的二进制文件和pos号,配置master 128的同步参数:
mysql> change master to
-> master_host='192.168.190.129',
-> master_user='mysync',
-> master_password='123456',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=318;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
根据第4步读取的二进制文件和pos号,配置master 129的同步参数:
mysql> change master to
-> master_host='192.168.190.128',
-> master_user='mysync',
-> master_password='123456',
-> master_log_file='mysql-bin.000004',
-> master_log_pos=906;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
6.查看slave同步状态(查看Slave_IO_Running和Slave_SQL_Running是否均为Yes):
master128:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.190.129
Master_User: mysync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 318
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-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: 318
Relay_Log_Space: 235
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
1 row in set (0.00 sec)
ERROR:
No query specified
master129:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.190.128
Master_User: mysync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 906
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000004
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: 906
Relay_Log_Space: 235
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
1 row in set (0.00 sec)
ERROR:
No query specified
- mysql 互为主备的简单搭建
- MySQL 数据同步 主主设置(互为主备)
- mysql 双机热备 主主同步 (互为主从)
- mysql主从复制之一备多主,互为主备
- Mysql+KeepAlived 主从搭建测试(互为主从)
- 让两台服务器的MySQL(5.7)数据同步_主主同步(互为主从关系)
- mysql 互为主从
- MySQL互为主从配置
- mysql互为主从
- mysql互为主从复制
- mysql 互为主从配置
- mysql互为主从复制
- KeepAlived+MySQL互为主从
- mysql互为主从配置
- Mysql主从复制的配置(双机互为主从)
- Mysql互为主从,解决主键ID冲突的方案
- Mysql主从复制的配置(双机互为主从)
- 搭建简单的servlet&MySQL
- NSArray和 Vector<struct>互转
- 標準C++的類型轉換:static_cast、dynamic_cast
- windows 环境变量
- SQL基础5
- 设计模式总结—创建型模式
- mysql 互为主备的简单搭建
- 快捷方式删除不成功的解决办法
- QT MD5 SHA1加密使用
- 完美解决Ubuntu下vi编辑器方向键变字母的问题
- 有一个无效 SelectedValue,因为它不在项目列表中。
- SpringMVC无法使用AOP拦截控制器
- 如何编译ffmpeg以支持dshow设备
- android消息推送
- pathon教程