MySQL主從同步

特別提醒: 做主從前最好在數據庫尚未創建的時候做，如果已經創建了。可以將原來數據導出，等建立好主從以后在主上面導入即可讓數據同步

1、環境

主數據庫 ?IP:10.5.100.159

從數據庫1 ?IP:10.5.100.160

從數據庫2 ?IP:10.5.100.180

2、配置

主數據庫配置my.cnf在[mysqld]下添加配置數據：server-id=1 #配一個唯一的ID編號，1至32。log-bin=mysql-bin #二進制文件存放路徑#設置要進行或不要進行主從復制的數據庫名，同時也要在Slave 上設定。binlog-do-db=數據庫名1binlog-do-db=數據庫名2binlog-ignore-db=數據庫名11binlog-ignore-db=數據庫名22保存，重啟數據庫服務。mysql mysql -u[用戶名] -p[密碼] 如： mysql -u root -p password在主數據庫中建立一個備份賬戶：mysql>grant replication slave,replication client,reload,super on *.* to slave@10.5.100.160 identified by 'password' ;

mysql>flush privileges;mysql>show master status; # 找到File 和 Position 的值記錄下來；從數據庫配置my.cnf[mysqld]server-id=2 #唯一#設置要進行或不要進行主從復制的數據庫名，同時也要在Master 上設定。replicate-do-db=數據庫名1replicate-do-db=數據庫名2replicate-ignore-db=數據庫名11replicate-ignore-db=數據庫名22保存，重啟數據庫服務。

注意：在mysql5.1以上版本中是不支持1中master設置的，如果添加了master設置，數據庫就無法重啟了；

在cmd下登錄數據庫：mysql mysql -u[用戶名] -p[密碼]mysql>change master to master_host='10.5.100.159',master_user='slave',master_password='password', master_log_file='mysql-bin.000001',master_log_pos=107;mysql> start slave;mysql> show slave status\G;如果出現：Slave_IO_Running: YesSlave_SQL_Running: Yes以上兩項都為Yes，那說明沒問題了。注：如果出現Slave_SQL_Running：connecting的現象

先查看err日志中是否有??這樣的報錯

141226 12:07:47 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). Error_code: 1593

如果是這個報錯。則是server-id 一樣了。mysql> show variables like 'server_id';

查看一下。如果是一樣，用下面命令修改。mysql> set global server_id=2;

mysql> start slave; ?啟動slavemysql> show slave status\G；查看一下狀態

3、從數據庫2 ?IP:10.5.100.180請參照2方法

Mysql主從同步常見異常及恢復方法

1. 一般的異常只需要跳過一步即可恢復

>slave stop;

>SET GLOBAL sql_slave_skip_counter = 1;

>slave start;

2.斷電導致主從不能同步時，通主庫的最后一個bin-log日志進行恢復

在主庫服務器上，mysqlbinlog mysql-bin.xxxx > binxxxx.txt

tail -n 100000 ?binxxxx.txt > tail-binxxxx.txt

vim tail-binxxxx.txt 打開tail-binxxxx.txt文件找到最后一個postion值

然后在從庫上，change host to 相應正確的值

>slave stop;

>change master to master_host='ip', master_user='username', master_password='password', master_log_file='mysql-bin.xxxx', master_log_pos=xxxx;

>slave start;

>show slave status\G;

3.主鍵沖突、表已存在等錯誤代碼如1062,1032,1060等，可以在mysql主配置文件指定

略過此類異常并繼續下條sql同步，這樣也可以避免很多主從同步的異常中斷

[mysqld]

slave-skip-errors = 1062,1032,1060

先寫到這，后續。

數據同步過程中出現錯誤也可以先

stop slave

然后用

set global sql_slave_skip_counter =1;

來解決

導入mysql的時候導入不全，提示 ：ERROR at line 810: Unknown command '\''.

解決方法：導出的時候加上參數 mysqldump --default-character-set=utf8導入的時候也加上 mysql --default-character-set=utf8

案例：

bin>mysql -uroot -p thinkwap --default-character-set=utf8

進入slave服務器，運行：

MySQL> show slave status\G

.......

Relay_Log_File: localhost-relay-bin.000535

Relay_Log_Pos: 21795072

Relay_Master_Log_File: localhost-bin.000094

Slave_IO_Running: Yes

Slave_SQL_Running:?No

Replicate_Do_DB:?Replicate_Ignore_DB:?......

解決辦法一、

Slave_SQL_Running: No

1.程序可能在slave上進行了寫操作

2.也可能是slave機器重起后，事務回滾造成的.

一般是事務回滾造成的：

解決辦法：

mysql>?stop?slave ;

mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql>?start?slave ;

解決辦法二、

首先停掉Slave服務：slave stop

到主服務器上查看主機狀態：

記錄File和Position對應的值

進入master

mysql> show master status;

+----------------------+----------+--------------+------------------+

| File?| Position | Binlog_Do_DB | Binlog_Ignore_DB |

+----------------------+----------+--------------+------------------+

| localhost-bin.000094 | 33622483 |?|?|?+----------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

然后到slave服務器上執行手動同步：

mysql> change master to?> master_host='master_ip',

> master_user='user',?> master_password='pwd',?> master_port=3306,?> master_log_file=localhost-bin.000094',?> master_log_pos=33622483;

1 row in set (0.00 sec)

mysql>?start?slave ;

1 row in set (0.00 sec)

mysql> show slave status\G

*************************** 1. row ***************************

........

Master_Log_File: localhost-bin.000094

Read_Master_Log_Pos: 33768775

Relay_Log_File: localhost-relay-bin.000537

Relay_Log_Pos: 1094034

Relay_Master_Log_File: localhost-bin.000094

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

手動同步需要停止master的寫操作！

測試的mysql數據表

CREATE TABLE `t_user` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',

`name` varchar(300) NOT NULL COMMENT '名稱',

`create_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '添加時間',

`update_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '更新時間',

`ip` varchar(20) NOT NULL COMMENT 'ip',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=91 DEFAULT CHARSET=utf8;

測試主從php程序如下：

/*session_start();

if(!$_SESSION['king_new_haha']){

$_SESSION['king_new_haha'] = 'king_new_haha';

}

var_dump($_SESSION['king_new_haha']);

echo 'salve01';*/

header('Content-type:text/html;charset=utf-8');

//如果提交了表單

//數據庫連接參數

$host = "10.5.100.159";

$user = "root";

$pass = "e139fb5459";

$db = "test";

//取得表單中的值，檢查表單中的值是否符合標準，并做適當轉義，防止SQL注入

//打開數據庫連接

$connection = mysql_connect($host, $user, $pass) or die("Unable to connect!");

//選擇數據庫

mysql_select_db($db) or die("Unable to select database!");

mysql_query('set names utf8');

//構造一個SQL查詢

$create_time = $update_time = time();

$name = 'jin哈哈'.date('Y-m-d H:i:s',$create_time);

$ip = $_SERVER['HTTP_HOST'];//$_SERVER["REMOTE_ADDR"];

$query = "INSERT INTO t_user(name, create_time, update_time,ip) VALUE('$name', '$create_time', '$update_time','$ip')";

file_put_contents('my.txt',$query);

//執行該查詢

$result = mysql_query($query) or die("Error in query: $query. ".mysql_error());

//插入操作成功后，顯示插入記錄的記錄號

echo "www.xjinshop.com::記錄已經插入， mysql_insert_id() = ".mysql_insert_id();

//關閉當前數據庫連接

mysql_close($connection);

?>

結果是寫入主庫，查看到兩個從庫也已經有數據

頂

總結

以上是生活随笔為你收集整理的mysql主从同步_utf8_MySQL主从同步的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。