mysql主从同步设置

主从同步配置步骤如下：环境：MySQL server 5.1.60 64位 CentOS 5.3 64 bit位

一、主服务器配置#

编辑主服务器配置文件/etc/my.cnf，如果该文件不存在请执行

 cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

【有关MySQL参数调优不在本文描述】

[mysqld] 加入:

max_connect_errors = 720000
binlog-do-db=DATABASE

检查以下选项：

log-bin=mysql-bin
server-id       = 1
binlog_format=mixed

开启innoDB 配置：

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

重新启动： mysql

service mysql start

在主服务器上创建主从同步用户帐号：

Create User rep_slave IDENTIFIED BY 'rep_slave';
GRANT REPLICATION SLAVE ON *.* TO 'DATABASE_slave'@'%';
FLUSH PRIVILEGES;

二、从服务器配置

停止服务器

service mysql stop

vi /etc/my.cnf

[mysqld] 加入：

max_connect_errors = 720000
server-id       = 2
master-host     =   192.168.1.244
master-user     =   micrblog_slave
master-password =   DATABASE
replicate-do-db=DATABASE
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
master-connect-retry=60
master-port=3306

开启innoDB 配置：

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

重新启动： mysql

service mysql start

三、数据导入到从服务器#

mysql -u root -p (log into MySQL)
FLUSH TABLES WITH READ LOCK;

从主服务器导出数据

mysqldump --default-character-set=utf8 -h192.168.1.244 -uroot -p　DATABASE >DATABASE-exp1.sql

将文件传到从服务器上并执行导入：

mysql --default-character-set=utf8 -h192.168.1.129 -uroot -p -D  DATABASE < DATABASE-exp1.sql

四、启动主从同步#

4.1查看主服务器bin-log状态#

SHOW MASTER STATUS;

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000002 |      106 | DATABASE    |                  |

+------------------+----------+--------------+------------------+

4.2 登录到从服务器执行#

  slave stop;

CHANGE MASTER TO MASTER_HOST=‘192.168.1.244’, MASTER_USER=‘micrblog_slave’, MASTER_PASSWORD=‘DATABASE’, MASTER_LOG_FILE=‘mysql-bin.000002’, MASTER_LOG_POS=106;

slave start;

4.3 检查从服务器状态#

show slave status