教你在MySQL 5.0以上版本中配置主从库

  下面直接记录下配置主从库的操作:(本文用的是mysql5.0以上)
  1.在主库建立要同步的数据库,建立主库的帐号和修改主库配置
  首先连接上数据库
  mysql -S /tmp/mysql-3108.sock
  创建测试同步的数据库,"create database sinatest;"
  然后建立负责同步的用户
  grant all on *.* to ali@"%" Identified by "abc111";
  然后修改/data2/ali/mysql3308/my.cnf主库的配置,增加
  binlog-do-db=sinatest
  2.修改从库配置
  然后修改/data2/ali/mysql3309/my.cnf从库的配置,增加
  master-host=127.0.0.1
  master-user=ali
  master-password=abc111
  master-port=3308
  server-id=2
  master-connect-retry=60
  replicate-do-db=sinatest
  log-slave-updates
  3.重启mysql
  先杀死mysql进程,然后再启动
  /usr/local/mysql/bin/mysqld_safe --defaults-file=/data2/ali/mysql3308/my.cnf --user=root &
  /usr/local/mysql/bin/mysqld_safe --defaults-file=/data2/ali/mysql3309/my.cnf --user=root &
  4.在主库创建数据表,检查从库是否同步正确
  进入主库,创建数据表
  mysql -S /tmp/mysql-3108.sock
  CREATE TABLE if not exists ali_test_00 (
  `id` int(11) unsigned NOT NULL auto_increment,
  `my_id` varchar(16) binary NOT NULL default '',
  `my_title` varchar(96) binary NOT NULL default '',
  `status` int(11) unsigned NOT NULL default '1',
  PRIMARY KEY (`id`),
  KEY `my_id` (`my_id`)
  ) TYPE=MyISAM;
  insert into ali_test_00 values (null,32,"aliwwww",1);
  insert into ali_test_00 values (null,32,"aliwwww",1);
  然后检查从库是否有该表和表中是否有数据
  5.检查主从库常用命令
  SHOW SLAVE STATUS;
  SHOW MASTER STATUS;
  slave stop;
  slave start;
  CHANGE MASTER TO
  MASTER_HOST='127.0.0.1',
  MASTER_USER='ali',
  MASTER_PASSWORD='abc111',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=0;