簡單的MySQL數據庫主從同步配置，mysql數據庫主從

簡單的MySQL數據庫主從同步配置，mysql數據庫主從

Master配置：

代碼如下 復制代碼
log-bin = mysql-bin
server-id = 1
binlog-do-db = powerdns #要同步的數據庫

Master新增slave賬號：

代碼如下 復制代碼
mysql> GRANT replication slave ON *.* TO 'slave'@'192.168.1.2' IDENTIFIED BY 'passwd';
mysql> FLUSH privileges;

重啟Master MySQL，查看Master狀態信息，記住File名稱，POS位置。

代碼如下 復制代碼
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+-------(www.111cn.net)-----------+
| mysql-bin.000002 | 309 | powerdns | mysql |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Slave配置：

代碼如下 復制代碼
server-id =2

開始同步：

代碼如下 復制代碼
mysql> CHANGE master TO master_host='192.168.1.1',master_user='slave',master_password='passwd',
master_log_file='mysql-bin.000002',master_log_pos=309;
mysql> START slave;

查看Slave同步狀態：

代碼如下 復制代碼
mysql> SHOW slave STATUSG;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

from:http://www.111cn.net/database/mysql/61293.htm

在配置mysql 的 主從數據庫同步的時，主數據庫名稱與從數據庫名稱一定要一樣？

用replication-rewrite-db= dbmaster > dbslave
5.0的版本這麼設置，高點的版本不知是否支持
 

mysql主從同步 怎同步已有的數據，

--主機開兩個窗口，一個進入mysql，一個是shell
--主機阻斷寫操作
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 1529881 | openser | mysql,test |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
--另外一個窗口導出主機數據庫
mysqldump -u root -p123456 --opt -R openser > openser20121203.sql
--剛才的窗口主機解鎖
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql>
--打包數據文件到從機
drop database openser;
create database openser;
mysql -u root -p123456 openser < openser20121127.sql
--從機操作
SLAVE STOP;
reset slave;
CHANGE MASTER TO MASTER_HOST='192.168.21.26',
MASTER_USER='repl_user',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=1529881;
start slave;
show slave status\G