1.在主庫建立要同步的數(shù)據(jù)庫,建立主庫的帳號和修改主庫配置

首先連接上數(shù)據(jù)庫

mysql -S /tmp/mysql-3108.sock

創(chuàng)建測試同步的數(shù)據(jù)庫,"create database sinatest;"

然后建立負(fù)責(zé)同步的用戶

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

先殺死m(xù)ysql進(jìn)程,然后再啟動

/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.在主庫創(chuàng)建數(shù)據(jù)表,檢查從庫是否同步正確

進(jìn)入主庫,創(chuàng)建數(shù)據(jù)表

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);

然后檢查從庫是否有該表和表中是否有數(shù)據(jù)

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;