mysql主从复制

1.先在主库上创建一个用于复制的用户

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO repl@'192.168.1.%' IDENTIFIED BY 'password';

2.修改主库上的my.cnf加上

  server_id = 6
  log_bin = mysql-bin
  sync_binlog=1
  # innodb数据库推荐加上这两个配置
  innodb_flush_log_at_trx_commit
  innodb_support_xa = 1

3.修改备库上的my.cnf,加上

log_bin = mysql-bin
server_id = 204   
relay_log = /var/lib/mysql/mysql-relay-bin
log_slave_updates = 1
read_only = 1
sync_master_info=1
sync_relay_log=1
sync_relay_log_info=1
master-info-repository  = TABLE
relay-log-info-repository = TABLE

4 输入命令开始备份

CHANGE MASTER TO MASTER_HOST = 'server1'
  MASTER_USER='repl'
  MASTER_PASSWORD='XXXX'
  MASTER_LOG_FILE='mysql-bin.000001'
  MASTER_LOG_POS=0  
  START SLAVE;