在这里吧昨天做的主从数据库配置记录下来,免得以后折腾
数据库主从配置心得:
master : 192.168.16.247
slave1 : 192.168.16.248
1 修改配置文件 /etc/mysql/my.cnf(如果my.cnf已有该配置项,则相应的进行修改)
主数据库:
server-id = 1
log-bin = mysql-bin
log-bin-index = mysql-bin.index
#log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = flyticket_safe
从数据库:
server-id = 2
master-host = 192.168.16.247[主数据库IP地址]
master-user = newbackup[连接主数据库的用户名]
master-password = backup[连接主数据库的密码]
master-port = 3306[端口]
master-connect-retry = 60
log-slave-updates
replicate-do-db = flyticket_safe[数据库]
log-bin = mysql-bin
binlog_format = mixed
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
2 在master机器上分别添加用户和授权,如:在247上执行:
GRANT REPLICATION SLAVE ON *.* TO 'newbackup'@'192.168.16.248' IDENTIFIED BY 'backup';
3 在master上执行:
flush tables with read lock;
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 1049 | | |
+------------------+----------+--------------+------------------+
unlock tables;
4 在slave上执行
change master to MASTER_HOST='192.168.16.235', MASTER_USER='newbackup247',MASTER_PASSWORD='backup',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=107;
[mysql-bin.000003 1049 改成主数据库中SHOW MASTER STATUS;的值]
slave start;
5 配置半同步:[有些不支持]
主库上安装:
install plugin rpl_semi_sync_master soname 'semisync_master.so';
查询是否安装成功:
SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME='rpl_semi_sync_master'\G
从库上安装:
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
主库上配置:
set global rpl_semi_sync_master_enabled=1;
set global rpl_semi_sync_master_timeout=1000;
从库上配置:
set global rpl_semi_sync_slave_enabled=1;
6 查看从数据库上的配置情况
show slave status \G