mysql 主从数据库配置_mysql主从数据库配置

数据库主从配置心得：

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