- 浏览: 91796 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
lunzi:
sqllite3 installhttp://www.supe ...
linux架设subversion(svn) -
lunzi:
libiconv.so.2: cannot open shar ...
linux架设subversion(svn)
环境:
master:192.168.2.142
slave :192.168.2.143
两者都装了mysql 5.6.10
硬件环境都一样,vmware下centos 6.2
内存 1GB
磁盘 20GB
CPU 2个
在master端创建测试数据库
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.10 Source distribution
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> create database kenyon_master;
Query OK, 1 row affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kenyon_master |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use kenyon_master;
Database changed
mysql> create table t_kenyon(id int);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t_kenyon values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t_kenyon;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> show master status;
Empty set (0.00 sec)
mysql> \q
Bye
[root@localhost ~]#
修改master端/etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id =1
binlog-do-db=kenyon_master
binlog-ignore-db=mysql,test,information_schema,performance_schema
bind-address = 0.0.0.0
innodb_data_home_dir = /usr/local/mysql/data/
innodb_log_group_home_dir = /usr/local/mysql/data/
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
重启master端DB,使修改生效,并创建slave端连到master端的用户
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL............ SUCCESS!
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.10-log Source distribution
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> show variables like 'innodb_data_home_dir';
+----------------------+------------------------+
| Variable_name | Value |
+----------------------+------------------------+
| innodb_data_home_dir | /usr/local/mysql/data/ |
+----------------------+------------------------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_additional_mem_pool_size';
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| innodb_additional_mem_pool_size | 20971520 |
+---------------------------------+----------+
1 row in set (0.00 sec)
mysql> show master stauts;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'stauts' at line 1
mysql> show master status;
+------------------+----------+---------------+--------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+---------------+--------------------------------------------------+-------------------+
| mysql-bin.000001 | 120 | kenyon_master | mysql,test,information_schema,performance_schema | |
+------------------+----------+---------------+--------------------------------------------------+-------------------+
1 row in set (0.02 sec)
mysql> grant replication slave on *.* to repluser@'192.168.2.143' identified by '123456';
Query OK, 0 rows affected (0.07 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye
[root@localhost ~]#
备份master端数据,并拷贝至slave端
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+---------------+--------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+---------------+--------------------------------------------------+-------------------+
| mysql-bin.000002 | 120 | kenyon_master | mysql,test,information_schema,performance_schema | |
+------------------+----------+---------------+--------------------------------------------------+-------------------+
1 row in set (0.00 sec)
mysql>
另外一个session:
[root@localhost log]# cd /usr/local/mysql/data
[root@localhost data]# tar -zcvf backup.tar.gz kenyon_master
kenyon_master/
kenyon_master/t_kenyon.frm
kenyon_master/t_kenyon.ibd
kenyon_master/db.opt
[root@localhost data]#
[root@localhost data]# scp backup.tar.gz root@192.168.2.143:/usr/local/mysql/data/
The authenticity of host '192.168.2.143 (192.168.2.143)' can't be established.
RSA key fingerprint is 21:85:2a:f7:28:41:61:57:68:6c:3b:47:0a:25:92:47.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.143' (RSA) to the list of known hosts.
root@192.168.2.143's password:
backup.tar.gz 100% 887 0.9KB/s 00:00
[root@localhost data]#
回到之前的session,解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
修改slave端的/etc/my.cnf
[mysqld]
server-id = 2
master-host = 192.168.2.142 #5.5以上废弃
master-user = repluser #5.5以上废弃
master-password = 123456 #5.5以上废弃
master-port = 3306 #5.5以上废弃
master-connect-retry=60 #重试时间60秒, #5.5以上废弃
replicate-do-db=kenyon_master #需要同步的库
log-slave-updates=1 #启用从库日志,这样可以设置链式复制
read-only = 1 #0表示可读写,1表示只读,但是只针对普通用户,超级用户和同步用户不受此限制
重启后查看状态:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.2.142
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 120
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB: kenyon_master
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: 120
Relay_Log_Space: 120
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: NULL
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: 0
Master_UUID:
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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)
上面有两个参数:Slave_IO_Running, Slave_SQL_Running的状态是NO,说明有问题,网络已经排查,我们重启slave后,发现正常了。
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.10 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.142
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 120
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: kenyon_master
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: 120
Relay_Log_Space: 460
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
Master_UUID: 707d5ebf-8493-11e2-9e08-000c29202821
Master_Info_File: /usr/local/mysql/data/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)
mysql>
这个时候master端的数据变化可以反映在slave端(在master库里新增删表或者修改数据,在slave端查看是否同步)。
master端的进程
mysql> show processlist\G
*************************** 1. row ***************************
Id: 12
User: root
Host: localhost
db: kenyon_master
Command: Sleep
Time: 1481
State:
Info: NULL
*************************** 2. row ***************************
Id: 20
User: root
Host: localhost
db: kenyon_master
Command: Query
Time: 0
State: init
Info: show processlist
*************************** 3. row ***************************
Id: 23
User: repluser
Host: 192.168.2.143:59811
db: NULL
Command: Binlog Dump
Time: 708
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
3 rows in set (0.02 sec)
slave端的进程
mysql> show processlist\G
*************************** 1. row ***************************
Id: 5
User: root
Host: localhost
db: kenyon_master
Command: Query
Time: 0
State: init
Info: show processlist
*************************** 2. row ***************************
Id: 10
User: system user
Host:
db: NULL
Command: Connect
Time: 602
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 11
User: system user
Host:
db: NULL
Command: Connect
Time: 282
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
3 rows in set (0.03 sec)
至此,一个master-slave的配置已经完成。
change master to master_host='172.168.2.148',master_user='repluser',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=120
如果需要做级联复制,则加一下
log-slave-updates=1
加完这个重启从机,查看是否生效:
mysql> show variables like 'log_slave_%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| log_slave_updates | ON |
+-------------------+-------+
1 row in set (0.00 sec)
配好了从机有可能需要重启一下
如果多个DB复制
binlog-do-db=DB1
binlog-do-db=DB2
binlog-do-db=DB3
replicate-do-db=DB1
replicate-do-db=DB2
replicate-do-db=DB3
master:192.168.2.142
slave :192.168.2.143
两者都装了mysql 5.6.10
硬件环境都一样,vmware下centos 6.2
内存 1GB
磁盘 20GB
CPU 2个
在master端创建测试数据库
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.10 Source distribution
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> create database kenyon_master;
Query OK, 1 row affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kenyon_master |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use kenyon_master;
Database changed
mysql> create table t_kenyon(id int);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t_kenyon values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t_kenyon;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> show master status;
Empty set (0.00 sec)
mysql> \q
Bye
[root@localhost ~]#
修改master端/etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id =1
binlog-do-db=kenyon_master
binlog-ignore-db=mysql,test,information_schema,performance_schema
bind-address = 0.0.0.0
innodb_data_home_dir = /usr/local/mysql/data/
innodb_log_group_home_dir = /usr/local/mysql/data/
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
重启master端DB,使修改生效,并创建slave端连到master端的用户
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL............ SUCCESS!
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.10-log Source distribution
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> show variables like 'innodb_data_home_dir';
+----------------------+------------------------+
| Variable_name | Value |
+----------------------+------------------------+
| innodb_data_home_dir | /usr/local/mysql/data/ |
+----------------------+------------------------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_additional_mem_pool_size';
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| innodb_additional_mem_pool_size | 20971520 |
+---------------------------------+----------+
1 row in set (0.00 sec)
mysql> show master stauts;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'stauts' at line 1
mysql> show master status;
+------------------+----------+---------------+--------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+---------------+--------------------------------------------------+-------------------+
| mysql-bin.000001 | 120 | kenyon_master | mysql,test,information_schema,performance_schema | |
+------------------+----------+---------------+--------------------------------------------------+-------------------+
1 row in set (0.02 sec)
mysql> grant replication slave on *.* to repluser@'192.168.2.143' identified by '123456';
Query OK, 0 rows affected (0.07 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye
[root@localhost ~]#
备份master端数据,并拷贝至slave端
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+---------------+--------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+---------------+--------------------------------------------------+-------------------+
| mysql-bin.000002 | 120 | kenyon_master | mysql,test,information_schema,performance_schema | |
+------------------+----------+---------------+--------------------------------------------------+-------------------+
1 row in set (0.00 sec)
mysql>
另外一个session:
[root@localhost log]# cd /usr/local/mysql/data
[root@localhost data]# tar -zcvf backup.tar.gz kenyon_master
kenyon_master/
kenyon_master/t_kenyon.frm
kenyon_master/t_kenyon.ibd
kenyon_master/db.opt
[root@localhost data]#
[root@localhost data]# scp backup.tar.gz root@192.168.2.143:/usr/local/mysql/data/
The authenticity of host '192.168.2.143 (192.168.2.143)' can't be established.
RSA key fingerprint is 21:85:2a:f7:28:41:61:57:68:6c:3b:47:0a:25:92:47.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.143' (RSA) to the list of known hosts.
root@192.168.2.143's password:
backup.tar.gz 100% 887 0.9KB/s 00:00
[root@localhost data]#
回到之前的session,解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
修改slave端的/etc/my.cnf
[mysqld]
server-id = 2
master-host = 192.168.2.142 #5.5以上废弃
master-user = repluser #5.5以上废弃
master-password = 123456 #5.5以上废弃
master-port = 3306 #5.5以上废弃
master-connect-retry=60 #重试时间60秒, #5.5以上废弃
replicate-do-db=kenyon_master #需要同步的库
log-slave-updates=1 #启用从库日志,这样可以设置链式复制
read-only = 1 #0表示可读写,1表示只读,但是只针对普通用户,超级用户和同步用户不受此限制
重启后查看状态:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.2.142
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 120
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB: kenyon_master
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: 120
Relay_Log_Space: 120
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: NULL
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: 0
Master_UUID:
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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)
上面有两个参数:Slave_IO_Running, Slave_SQL_Running的状态是NO,说明有问题,网络已经排查,我们重启slave后,发现正常了。
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.10 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.142
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 120
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: kenyon_master
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: 120
Relay_Log_Space: 460
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
Master_UUID: 707d5ebf-8493-11e2-9e08-000c29202821
Master_Info_File: /usr/local/mysql/data/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)
mysql>
这个时候master端的数据变化可以反映在slave端(在master库里新增删表或者修改数据,在slave端查看是否同步)。
master端的进程
mysql> show processlist\G
*************************** 1. row ***************************
Id: 12
User: root
Host: localhost
db: kenyon_master
Command: Sleep
Time: 1481
State:
Info: NULL
*************************** 2. row ***************************
Id: 20
User: root
Host: localhost
db: kenyon_master
Command: Query
Time: 0
State: init
Info: show processlist
*************************** 3. row ***************************
Id: 23
User: repluser
Host: 192.168.2.143:59811
db: NULL
Command: Binlog Dump
Time: 708
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
3 rows in set (0.02 sec)
slave端的进程
mysql> show processlist\G
*************************** 1. row ***************************
Id: 5
User: root
Host: localhost
db: kenyon_master
Command: Query
Time: 0
State: init
Info: show processlist
*************************** 2. row ***************************
Id: 10
User: system user
Host:
db: NULL
Command: Connect
Time: 602
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 11
User: system user
Host:
db: NULL
Command: Connect
Time: 282
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
3 rows in set (0.03 sec)
至此,一个master-slave的配置已经完成。
change master to master_host='172.168.2.148',master_user='repluser',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=120
如果需要做级联复制,则加一下
log-slave-updates=1
加完这个重启从机,查看是否生效:
mysql> show variables like 'log_slave_%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| log_slave_updates | ON |
+-------------------+-------+
1 row in set (0.00 sec)
配好了从机有可能需要重启一下
如果多个DB复制
binlog-do-db=DB1
binlog-do-db=DB2
binlog-do-db=DB3
replicate-do-db=DB1
replicate-do-db=DB2
replicate-do-db=DB3
相关推荐
Master-Slave的数据库机构解决了很多问题,特别是read/write比较高的web2.0应用: 1、写操作全部在Master结点执行,并由Slave数据库结点定时(默认60s)读取Master的bin-log 2、将众多的用户读请求分散到更多的...
里面实现的是动态切换数据源,大家都是知道,spring2之后添加AbstractRoutingDataSource这个东西,这个就可以实现切换数据源,实现思路是:先按照搭建MySQL的MasterSlave架构.doc>>(我上传有资源),搭建好,然后动态...
MySQL的Master Slave架构,手把手教你怎么操作,每一步操作都带有图
MYSQL的masterslave数据同步配置
Openstack中所有的组件均使用同一个mysql数据库,存在单点故障问题,如果mysql停止运行将影响整个云的稳定性;本文主要介绍如何构建基本的Mysql ha高可用环境,从而提高整个云的稳定性。
mysql master-slave 主从配置的讲解
主要为大家详细介绍了MYSQL5.6.33数据库主从(Master/Slave)同步安装与配置,具有一定的参考价值,感兴趣的小伙伴们可以参考一下
用于监控MySQL Master Slave 状态的python代码,有需要的朋友可以参考下
本篇文章是对MySQL Master Slave 同步配置进行了详细的分析介绍,需要的朋友参考下
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs This error generally occur when we clone the master to slaver. Delete auto.cnf of mysql, and then ...
mysql5.5 master-slave(Replication)配置方法,需要的朋友可以参考下。
该配置文件可以直接放置到目录下,使用docker-compose up -d启动使用。 其中server_id=XX不能重复 在master中执行: grant replication slave,replication client on *.* to 'user1'@'%' identified by '1'; ...
mysql slave切换为master 测试
使用allen老师的hub镜像制作自己的mysql主从 --------- docker pull xiaochunping/mysql-master;...slave配置文件/home/mysql/etc/slave/slave.cnf mkdir -p /home/mysql/master-data mkdir -p /home/mysql/slave-data
mysql主从配置master和slave模式,
本篇文章是对MySQL数据库复制Master-Slave架构进行了详细的分析介绍,需要的朋友参考下
此资源为shardingsphere 调研...内容:一个master mysql docker实例,一个slave docker实例 内置3个库,mydb,mydb0,mydb1。仅开启了这三个库的主从。如果三个库主从不够用,自行配置。 使用:1.解压 2.执行build.sh
如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致slave mysql容器配置配置Dockerfile设置server_id,一般设置为
一主一从: Master: OS:centos release 5.6 DB:mysql 5.5.8 IP:192.168.1.2 Slave: OS:centos release 5.6 DB:mysql 5.5.8 IP:192.168.1.3 修改主机Master配置文件 (/etc/my.cnf) 代码如下: [mysqld] //至少要有...