教你在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;