下面直接記錄下配置主從庫的操作:(本文用的是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;
