mysql主从简易配置
1 master配置
1.1 my.conf
server-id=1 #主从不能一样
log-bin = /data/mysqllog/mysql-bin.log
binlog-do-db=jiradb #需要同步的db
binlog-ignore-db=mysql
binlog-ignore-db=test
重启myqsql:service mysqld restart
1.2 创建同步用户
#replication user
GRANT REPLICATION SLAVE ON *.* to 'replication_user'@'192.168.8.100' IDENTIFIED BY '123';
FLUSH PRIVILEGES;
GRANT Select ON jiradb.* TO 'replication_user'@'192.168.8.100';
GRANT RELOAD ON jiradb.* TO 'replication_user'@'192.168.8.100';
GRANT SUPER ON *.* TO 'replication_user'@'192.168.8.100';
FLUSH PRIVILEGES;
2 slave配置
2.1 my.cnf
server-id=2
relay-log-index=/data/mysqllog/slave-relay-bin.index
relay-log=/data/mysqllog/slave-relay-bin
slave-net-timeout = 60
master-connect-retry = 60
3.初始数据导入
3.1 主库导出
mysql -uroot -pjira
FLUSH TABLES WITH READ LOCK;
use jiradb;
mysqldump -uroot -pjira jiradb --opt > jiradb20130105.sql
scp jiradb20130105.sql test@192.168.8.100:/tmp/
3.2 从库导入
mysql -uroot -pjira < jiradb20130105.sql
3.3 从库参数配置
sql>
slave stop;
change master to master_host=' 192.168.8.100 ',
master_user='replication_user',
master_password='replication#user',
master_log_file='mysql-bin.000001',
master_log_pos=236776;
start slave;
SHOW SLAVE STATUS \G
3.4 主库取消锁定
mysql -uroot -pjira
UNLOCK TABLES;
4.重新slave上的配置
reset slave
5、跳过1146错误
mysql> stop slave;
mysql> set global sql_slave_skip_counter=1146;
mysql> start slave;