mysql双主设置
来源:互联网 发布:淘宝钻位展示 编辑:程序博客网 时间:2024/06/14 23:51
1.前台通过管理平台创建数据库;
2.备份数据库,并还原到另一台主机;
备份同时要备份表和 函数 结构
[root@vm702 ~]# mysqldump -uroot -p hetestdb > testdb.1.sql
[root@vm702 ~]# mysqldump -uroot -p -ntd -R hetestdb >> testdb.1.sql
导入时,可能会报错,
ERROR 1418 (HY000) at line 31: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
需要执行
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
然后导入不会提示错误
创建同名数据库。
[root@vm703 dump]# mysql -u root -p hetestdb < testdb.1.sql
3. 配置/etc/my.cnf
配置两台主机的配置文件
[mysqld]
event_scheduler=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
innodb_buffer_pool_size=256M
log_bin_trust_function_creators=1
log-bin=mysql52-bin
relay-log=relay-bin
read-only=0
relay-log-index=relay-bin-index
server_id=152
binlog-do-db=zbkc1207001
replicate-do-db=zbkc1207001
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
另外一台:
[mysqld]
event_scheduler=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
innodb_buffer_pool_size=256M
log_bin_trust_function_creators=1
log-bin=mysql52-bin
relay-log=relay-bin
read-only=0
relay-log-index=relay-bin-index
server_id=52 //这个值要不同。
binlog-do-db=zbkc1207001
replicate-do-db=zbkc1207001
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
4.先配置一台,
首先查看一台master:
mysql> show master status;
+--------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| mysql52-bin.000016 | 120 | zbkc1207001 | | |
+--------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
配置另外一台同步:
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.70.2',master_user='root',master_password='123456', master_port=3306, master_log_file='mysql52-bin.000016', master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
报错,处理方法
mysql> reset slave; //重新设置slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host='192.168.70.2',master_user='root',master_password='123456', master_port=3306, master_log_file='mysql52-bin.000016', master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
Relay_Master_Log_File: mysql52-bin.000016
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: zbkc1207001
两个YES表示同步成功。
配置另外一台,同样操作。 master_host写对方IP master_log_file master_log_pos写成对方show master status;查询值。
2.备份数据库,并还原到另一台主机;
备份同时要备份表和 函数 结构
[root@vm702 ~]# mysqldump -uroot -p hetestdb > testdb.1.sql
[root@vm702 ~]# mysqldump -uroot -p -ntd -R hetestdb >> testdb.1.sql
导入时,可能会报错,
ERROR 1418 (HY000) at line 31: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
需要执行
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
然后导入不会提示错误
创建同名数据库。
[root@vm703 dump]# mysql -u root -p hetestdb < testdb.1.sql
3. 配置/etc/my.cnf
配置两台主机的配置文件
[mysqld]
event_scheduler=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
innodb_buffer_pool_size=256M
log_bin_trust_function_creators=1
log-bin=mysql52-bin
relay-log=relay-bin
read-only=0
relay-log-index=relay-bin-index
server_id=152
binlog-do-db=zbkc1207001
replicate-do-db=zbkc1207001
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
另外一台:
[mysqld]
event_scheduler=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
innodb_buffer_pool_size=256M
log_bin_trust_function_creators=1
log-bin=mysql52-bin
relay-log=relay-bin
read-only=0
relay-log-index=relay-bin-index
server_id=52 //这个值要不同。
binlog-do-db=zbkc1207001
replicate-do-db=zbkc1207001
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
4.先配置一台,
首先查看一台master:
mysql> show master status;
+--------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| mysql52-bin.000016 | 120 | zbkc1207001 | | |
+--------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
配置另外一台同步:
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.70.2',master_user='root',master_password='123456', master_port=3306, master_log_file='mysql52-bin.000016', master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
报错,处理方法
mysql> reset slave; //重新设置slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host='192.168.70.2',master_user='root',master_password='123456', master_port=3306, master_log_file='mysql52-bin.000016', master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
Relay_Master_Log_File: mysql52-bin.000016
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: zbkc1207001
两个YES表示同步成功。
配置另外一台,同样操作。 master_host写对方IP master_log_file master_log_pos写成对方show master status;查询值。
0 0
- mysql双主设置
- mysql设置
- mysql设置
- mysql设置
- MySQL设置
- 关于mysql主主同步的设置
- Mysql Master-Master Replication MYSQL数据库主主同步设置
- wamp设置---mysql密码设置
- 【mysql】深入Mysql字符集设置
- 【mysql】设置mysql默认密码
- [mysql]mysql-5.6字符集设置
- 【mysql】mysql局域网访问设置
- linux安装mysql、卸载mysql、设置mysql
- 设置 MySql 数据同步
- mysql设置权限
- 设置 MySql 数据同步
- 设置 MySql 数据同步
- 设置 MySql 数据同步
- CoreImage
- Java:DocumentBuilderFactory调用XML的方法实例
- idea快捷键
- 博客说明
- 安卓开发,程序多语言
- mysql双主设置
- 2015-12-7 项目1—哈希表及其运算的实现
- Spring整合Ehcache缓存
- 函数formatDatetime的使用及说明
- iOS App开发文档--请求加解密方案概述
- android(38)(加载大图到内存)
- 记一次新旧系统数据迁移
- 第十五周项目4—直接插入排序
- 设计模式——抽象工厂之反射“+”