外鍵約束
1 什么是外鍵約束
foreign key 就是表與表之間的某種約定的關系，由于這種關系的存在，我們能夠讓表與表之間的數(shù)據(jù)，更加的完整，關連性更強。
關于完整性，關連性我舉個例子。
有二張表，一張是用戶表，一張是訂單表：
1》如果我刪除了用戶表里的用戶，那么訂單表里面根這個用戶有關的數(shù)據(jù)，就成了無頭數(shù)據(jù)了，不完整了。
2》如果我在訂單表里面，隨便插入了一條數(shù)據(jù)，這個訂單在用戶表里面，沒有與之對應的用戶。這樣數(shù)據(jù)也不完整了。
如果有外鍵的話，就方便多了，可以不讓用戶刪除數(shù)據(jù)，或者刪除用戶的話，通過外鍵同樣刪除訂單表里面的數(shù)據(jù)，這樣也能讓數(shù)據(jù)完整。
創(chuàng)建外鍵約束
外鍵： 每次插入或更新時，都會檢查數(shù)據(jù)的完整性。
方法一：通過 create table 創(chuàng)建外鍵
語法：
create table 數(shù)據(jù)表名稱(
...,
[CONSTRAINT [ 約束名稱 ]] FOREIGN KEY [ 外 鍵 字 段 ]
REFERENCES [ 外鍵表名 ]( 外 鍵 字 段 ， 外 鍵 字 段 2…..)
[ON DELETE CASCADE ]
[ON UPDATE CASCADE ]
)
CONSTRAINT constraint [k?n?stre?nt] 約束
REFERENCES reference [?refr?ns] 引用，參考
關于參數(shù)的解釋：
RESTRICT: 拒絕對父表的刪除或更新操作。
CASCADE: 從父表刪除或更新且自動刪除或更新子表中匹配的行。ON DELETE CAS]CADE 和 ON
UPDATE CASCADE 都可用
注意：on update cascade 是級聯(lián)更新的意思，on delete cascade 是級聯(lián)刪除的意思，意思就是說當你更新或刪除主鍵表，那外見表也會跟隨一起更新或刪除。
cascade[英][k??ske?d] 級聯(lián)
精簡化后的語法：
語法：foreign key 當前表的字段 references 外部表名 （關聯(lián)的字段） type=innodb
注：創(chuàng)建成功，必須滿足以下 4 個條件：
1、確保參照的表和字段存在。
2、組成外鍵的字段被索引。
3、必須使用 type 指定存儲引擎為：innodb.
4、外鍵字段和關聯(lián)字段，數(shù)據(jù)類型必須一致。
例：
用戶表，表名 user
創(chuàng)建 user 表：
CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment ,
`name` varchar(50) NOT NULL default '',
`sex` int(1) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=innodb;
[#]創(chuàng)建時，如果表名是 sql 關鍵字，使用時，需要使用反引號`
插入數(shù)據(jù)：
mysql> insert into user (name,sex)values("tank",1),("zhang",2);
mysql> select * from `user` ;
創(chuàng)建訂單表 如下：
mysql> CREATE TABLE `order` (
`order_id` int(11) NOT NULL AUTO_INCREMENT,
`u_id` int(11) DEFAULT '0',
`username` varchar(50) DEFAULT NULL,
`money` int(11) DEFAULT NULL,
PRIMARY KEY (`order_id`),
KEY `u_id` (`u_id`),
CONSTRAINT `order_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
注：on delete cascade on update cascade 添加級聯(lián)刪除和更新：
注：確保參照的表 user 中 id 字段存在。 組成外鍵的字段 u_id 被索引。 必須使用 type 指定存儲引擎為：innodb。
“ FOREIGN KEY order_f_key (u_id)” 中 order_f_key 為外鍵索引名稱
外鍵字段和關聯(lián)字段，數(shù)據(jù)類型必須一致。
在 order 表中插入用戶 tank 和 zhang 的數(shù)據(jù)：
mysql> INSERT INTO `order` (`u_id`, `username`, `money`) VALUES ('1', 'tank','2222' );
mysql> INSERT INTO `order` (`u_id`, `username`, `money`) VALUES ('2', 'zhang','2222' );
互動： select * from order; 執(zhí)行不成功的，需要添加`` .
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'order' at line 1
解決：
mysql> select * from order;
測試級聯(lián)刪除
mysql> delete from user where id =1; //刪除用戶名為 1 記錄
查看 order 表中 u_id 為 1 的記錄已經(jīng)被刪除了。
mysql> delete from user where id=1;
Query OK, 1 row affected (0.07 sec)
mysql> select * from order;
+----------+------+----------+-------+
| order_id | u_id | username | money |
+----------+------+----------+-------+
| 2 | 2 | zhang | 2222 |
+----------+------+----------+-------+
1 row in set (0.00 sec)測試級聯(lián)更新:
更新前的數(shù)據(jù)：
mysql> select * from order;
+----------+------+----------+-------+
| order_id | u_id | username | money |
+----------+------+----------+-------+
| 2 | 2 | tank | 2222 |
+----------+------+----------+-------+
mysql> select * from user;
+----+-------+-----+
| id | name | sex |
+----+-------+-----+
| 2 | zhang | 2 |
+----+-------+-----+
將 user 表中 zhang 的 id 改為 5.
mysql> update user set id=5 where id=2;
mysql> select * from order; #查看 order 表中的數(shù)據(jù)也更新為 5 了
測試數(shù)據(jù)完整性：
//下面在 order 里面插入一條數(shù)據(jù) u_id 為 6 用戶，在 user 表里面根本沒有，所以插入不進去。
mysql> INSERT INTO `order` (`u_id`, `username`, `money`) VALUES ('6', 'good','123');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bb`.`order`,
CONSTRAINT `order_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `user` (`id`))
mysql> insert into user values(6,"grace",2); #現(xiàn)在在 user 中添加一個 6
mysql> INSERT INTO `order` (`u_id`, `username`, `money`) VALUES ('6', 'good','2222' );
mysql> select * from `order`;外鍵約束，order 表受 user 表的約束
在 order 里面插入一條數(shù)據(jù) u_id 為 5 用戶，在 user 表里面根本沒有，所以插入不進去
mysql> insert into user values(5,'Find',1);
mysql> insert into `order` (u_id,username,money)values(5,'Find',346);
2.通過 alter table 創(chuàng)建外鍵和級聯(lián)更新，級聯(lián)刪除
alter table 數(shù)據(jù)表名稱 add [constraint [約束名稱] ] foreign key (外鍵字段,..) references 數(shù)據(jù)表(參照字段,...)
[on update cascade|set null|no action]
[on delete cascade|set null|no action]
mysql> create table order1(
o_id int(11) auto_increment, u_id int(11) default '0',
username varchar(50),
money int(11),
primary key(o_id),
index(u_id)
)engine=InnoDB;
mysql> alter table order1 add foreign key(u_id) references user(id) on delete cascade on update cascade,engine=innodb;
mysql> alter table order1 add foreign key(u_id) references user(id) on delete cascade on update cascade;
mysql> alter table order1 add constraint `bk`foreign key(u_id) references user(id) on delete cascade on update cascade,engine=innodb;
指定外鍵名稱一定要記得帶上
innodbmysql> show create table order1;
刪除外鍵
alter table 數(shù)據(jù)表名稱 drop foreign key 約束（外鍵）名稱
mysql> alter table order1 drop foreign key order1_ibfk_1;
mysql> show create table order1;
三、什么是視圖
視圖：是一張?zhí)摂M表，由 select 語句指定的數(shù)據(jù)結構和數(shù)據(jù)。不生成真實的文件。
語法：create view 視圖名稱（即虛擬的表名） as select 語句。
我們在怎樣的場景使用它，為什么使用視圖？
如果某個查詢結果出現(xiàn)的非常頻繁，也就是，要經(jīng)常拿這個查詢結果來做子查詢，這種視圖能夠簡化用戶的操作視圖機制，用戶可以將注意力集中在所關心的數(shù)據(jù)上。如果這些數(shù)據(jù)不是直接來自基本表，則可以通過定義視圖，使數(shù)據(jù)庫看起來結構簡單、清晰，并且可以簡化用戶的數(shù)據(jù)查詢操作
視圖是用戶能以不同的角度看待同樣的數(shù)據(jù)。
對于固定的一些基本表，我們可以給不同的用戶建立不同的視圖，這樣不同的用戶就可以看到自己需要的信息了。
視圖對重構數(shù)據(jù)庫提供了一定程度的邏輯性。
比如原來的 A 表被分割成了 B 表和 C 表，我們?nèi)匀豢梢栽?B 表和 C 表的基礎上構建一個視圖 A，而使用該數(shù)據(jù)表的程序可以不變。
視圖能夠?qū)C密數(shù)據(jù)提供安全保護
比如說，每門課的成績都構成了一個基本表，但是對于每個同學只可以查看自己這門課的成績，因此可以為每個同學建立一個視圖，隱藏其他同學的數(shù)據(jù)，只顯示該同學自己的適當?shù)睦靡晥D可以更加清晰的表達查詢數(shù)據(jù)。
有時用現(xiàn)有的視圖進行查詢可以極大的減小查詢語句的復雜程度。創(chuàng)建視圖
語法：create view 視圖名稱（即虛擬的表名） as select 語句。
我們在 book 數(shù)據(jù)庫中操作
mysql> create view bc as select b.bName ,b.price ,c.bTypeName from books as b left join category as c
on b.bTypeId=c.bTypeId;
試圖可以按照普通表去訪問。
另外視圖表中的數(shù)據(jù)和原數(shù)據(jù)表中數(shù)據(jù)時同步的。
show create view bc; 查看試圖的創(chuàng)建信息。
mysql> show create view bc;
----------------------------------------------------+----------------------+----------------------+
| bc | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bc`
AS select `b`.`bName` AS `bName`,`b`.`price` AS `price`,`c`.`bTypeName` AS `bTypeName` from (`books` `b`
left join `category` `c` on((`b`.`bTypeId` = `c`.`bTypeId`))) | latin1 | latin1_swedish_ci |
+------+------------------------------------------------------------
例：使用視圖查看數(shù)據(jù)
mysql> select * from bc \G
。。。
*************************** 44. row ***************************
bName: XML 完全探索
price: 104
bTypeName: 網(wǎng)站
等同于：
mysql> select b.bName ,b.price ,c.bTypeName from books as b left join category as c on b.bTypeId=c.bTypeId ;
更新或修改視圖
語法：
alter view 視圖名稱（即虛擬的表名） as select 語句。
update view 視圖名稱（即虛擬的表名）set
mysql> alter view bc as select b.bName ,b.publishing ,c.bTypeId from books as b left join category as c on b.bTypeId=c.bTypeId ;更新
mysql> update bc set bName='HA' where bTypeId=34;
表中沒有價格，所以不顯示
刪除試圖：
drop view 視圖名。
例：
mysql> drop view bc;
創(chuàng)建普通用戶的三種方法：
3 刪除用戶：
drop user 用戶名@localhost。
msyql 主從復制服務概述：
主從復制
復制解決的基本問題是讓一臺服務器的數(shù)據(jù)和另外的服務器保持同步。
一臺主服務器可以連接多臺從服務器，并且從服務器也可以反過來作主服務器。
主服務器和從服務器可以位于不同的網(wǎng)絡拓撲中，還能對整臺服務器、特定的數(shù)據(jù)庫，甚至特定的表進行復制。
主從服務器的版本必須一致，即使不一致，主服務器版本可以是舊的，從服務器必須是新的版本。
原理：
MYSQL 支持單向、異步復制，復制過程中一個服務器充當主服務器，而一個或多個其它服務器充當從服務器。主服務器將更新寫入二進制日志文件，并維護日志文件的一個索引以跟蹤日志循環(huán)。當一個從服務器連接到主服務器時，它通知主服務器從服務器在日志中讀取的最后一次成功更新的位置。從服務器接收從那時起發(fā)生的任何更新，然后封鎖并等待主服務器通知下一次更新。
注意:進行復制時，所有對復制中的表的更新必須在主服務器上進行。以避免后期對主服務器上的表進行的更新與對從服務器上的表所進行的更新之間的沖突。
1.1 mysql 支持的復制類型：
（１）：基于語句的復制： 在主服務器上執(zhí)行的 SQL 語句，在從服務器上執(zhí)行同樣的語句。MySQL 默認采用基于語句的復制，效率比較高。
（２）：一旦發(fā)現(xiàn)沒法精確復制時， 會自動選著基于行的復制?；谛械膹椭?，把改變的內(nèi)容復制過去，而不是把命令在從服務器上執(zhí)行一遍. 從 mysql5.0 開始支持
（３）：混合類型的復制: 默認采用基于語句的復制，一旦發(fā)現(xiàn)基于語句的無法精確的復制時，就會采用基于行的復制。
1.2 . 復制解決的問題
MySQL 復制技術有以下一些特點：
(1) 數(shù)據(jù)分布 (Data distribution )
(2) 負載平衡(load balancing)
(3) 數(shù)據(jù)備份(Backups) 遠程容災
(4) 高可用性和故障轉移 High availability and failover
(5) 升級測試
1.3 復制如何工作
整體上來說，復制有 3 個步驟：
(1) master 將改變記錄到二進制日志(binary log)中（這些記錄叫做二進制日志事件，binary log events）；
(2) slave 將 master 的 binary log events 拷貝到它的中繼日志(relay log)；
(3) slave 重做中繼日志中的事件，修改 salve 上的數(shù)據(jù)。binary log bin-log 日志
Iothread I/0 進程
relay log 中繼日志
SQL thread SQL 線程，作用：重放中繼日志里面的記錄
Date changes 數(shù)據(jù)更改
Master：主庫
Slave 備庫
mysql 主從復制中：
第一步：master 記錄二進制日志。在每個事務更新數(shù)據(jù)完成之前，master 在二進制日志記錄這些改變。MySQL 將事務寫入二進制日志，即使事務中的語句都是交叉執(zhí)行的。在事件寫入二進制日志完成后，master 通知存儲引擎提交事務。
第二步：slave 將 master 的 binary log 拷貝到它自己的中繼日志。首先，slave 開始一個工作線程——I/O 線程。I/O 線程在 master 上打開一個普通的連接，然后開始 binlog dump process。Binlog dump process 從master 的二進制日志中讀取事件，如果已經(jīng)執(zhí)行完 master 產(chǎn)生的所有文件，它會睡眠并等待 master 產(chǎn)生新的件。I/O 線程將這些事件寫入中繼日志。
第三步：SQL slave thread（SQL 從線程）處理該過程的最后一步。SQL 線程從中繼日志讀取事件，并重新執(zhí)行其中的事件而更新 slave 的數(shù)據(jù)，使其與 master 中的數(shù)據(jù)一致。只要該線程與 I/O 線程保持一致，中繼日志通常會
位于 OS 的緩存中，所以中繼日志的開銷很小。
此外，在 master 中也有一個工作線程和其它 MySQL 的連接一樣，slave 在 master 中打開一個連接也會使得master 開始一個線程。復制過程有一個很重要的限制——復制在 slave 上是串行化的，也就是說 master 上的并行更
新操作不能在 slave 上并行操作。常見的復制拓撲
一主庫多備庫
主主
主庫—分發(fā)主庫—多個備庫：采用 blackhcle 存儲引擎的分發(fā)主庫
模式： C/S 模式
端口：3306
xuexi63 主 mysql 服務器配置
創(chuàng)建要同步的數(shù)據(jù)庫：
mysql> create database tree;
mysql> use tree;
mysql> create table test1 (id int);
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| tree |
| mysql |
| test |
停止
[#] service mysqld stop
編輯
[#] vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
[#] Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0 #在原配置文件中，添加以下內(nèi)容：
log-bin=mysqllog #啟用二進制日志，默認存在/var/lib/mysql 下面
server-id=1 #本機數(shù)據(jù)庫 ID 標示。其中 master_id 必須為 1 到 232 之間的一個正整數(shù)值
binlog-do-db=tree #可以被從服務器復制的庫。二進制需要同步的數(shù)據(jù)庫名
[#] binlog-ignore-db=tree2 不可以被從服務器復制的庫重新啟動
[#] service mysqld restart
授權
mysql> grant replication slave on . to slave@192.168.1.64 identified by "123456";
查看狀態(tài)，信息
mysql> show master status;
+-----------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+
| mysqllog.000001 | 242 | tree | |
+-----------------+----------+--------------+------------------+
1 row in set (0.00 sec)
查看二進制日志位置：
[root@xuexi63 ~]# ls /var/lib/mysql/
ibdata1 ib_logfile1 mysql mysqllog.index passwd
ib_logfile0 tree mysqllog.000001 mysql.sock test
mysql>show binlog events \G
復制前保證兩個數(shù)據(jù)庫數(shù)據(jù)一致：
mysqldump -u root -p123456 -A >all.sql
把數(shù)據(jù)傳給從：
方法 1：scp all.sql 192.168.1.64:/root
方法 2：使用 nc 命令
NetCat，它短小精悍、功能實用，被設計為一個簡單、可靠的網(wǎng)絡工具，可通過 TCP 或 UDP 協(xié)議傳輸讀寫數(shù)據(jù)。同
時，它還是一個網(wǎng)絡應用 Debug 分析器，因為它可以根據(jù)需要創(chuàng)建各種不同類型的網(wǎng)絡連接。
語法：
服務器端：nc 發(fā)送數(shù)據(jù)的語法： nc -l 端口 < 要傳輸?shù)奈募?br>
客戶端： nc 接受數(shù)據(jù)的語法： nc 遠程 nc 服務器端 IP 端口 > 文件名
例：監(jiān)聽 9999 端口，當有客戶端連接時，就把對應文件傳送到來連接的客戶端
開啟監(jiān)聽：
[root@xuexi63 ~]#nc -l 9999 < all.sql
[root@xuexi63 ~]# netstat -antup | grep 9999tcp 0 0 0.0.0.0:9999 0.0.0.0:* LISTEN 15164/nc
測試接收：
xuexi64 ：
[root@xuexi64 ~]# cd /opt/
[root@xuexi64 opt]# ls
[root@xuexi64 opt]# nc 192.168.1.63 9999 > ncall.sql
[root@xuexi64 opt]# diff ncall.sql /root/all.sql
nc 除了傳輸文件，也可以傳輸字符串
使用 nc 命令發(fā)送字符：
發(fā)送端： echo "hello hacker！" | nc -l 5140
接受端：
[root@xuexi63 ~]# nc 192.168.1.63 5140
hello hacker！
mysql 從服務器: xuexi64
[root@xuexi64 opt]# yum install mysql-server -y
[root@xuexi64 opt]# service mysqld start
mysql>show variables like '%version%';查看版本
+-------------------------+---------------------+
| Variable_name | Value |
+-------------------------+---------------------+
| protocol_version | 10 |
| version | 5.1.52 |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | redhat-linux-gnu |
+-------------------------+---------------------+
5 rows in set (0.03 sec)
測試連接到主服務器是否成功
[root@xuexi64 opt]# mysql -u slave -h 192.168.1.63 -p123456
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+看不到 tree 數(shù)據(jù)庫
導入數(shù)據(jù)庫，和主服務器保持一致
[root@xuexi64 ~]# mysql -u root -p < all.sql
從服務器沒必要開 bin-log 日志注。
修改從服務器配置文件：
[root@xuexi64 ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
[#] Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[#]在配置文件中寫入以下內(nèi)容
server-id=2 #從服務器 ID 號，不要和主 ID 相同 ，如果設置多個從服務器，每個從服務器必須有一個唯一的 server
id 值，必須與主服務器的以及其它從服務器的不相同?？梢哉J為 server-id 值類似于 IP 地址：這些 ID 值能唯一識別
復制服務器群集中的每個服務器實例。
master-host=192.168.1.63 #指定主服務器 IP 地址
master-user=slave #制定在主服務器上可以進行同步的用戶名
master-password=123456 #密碼
[####]以下可以不寫
master-port = 3306 #同步所用的端口
master-connect-retry=60 #斷點重新連接時間
保存，重啟
[#]service mysqld restart
如果只做為備庫，就只設置 server-ID， 如果他也作為一個分發(fā)主庫，開啟 bin-log 和中繼日志
測試：
主服務器上查看：
mysql> show master status;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 2
Current database: tree1
+-----------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+| mysqllog.000004 | 106 | tree1 | |
+-----------------+----------+--------------+------------------+
從服務器上查看：
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.63
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqllog.000001
Read_Master_Log_Pos: 315
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 459
Relay_Master_Log_File: mysqllog.000001
Slave_IO_Running: Yes #可以看到這兩個 Yes，說明從服務器安裝成功。
Slave_SQL_Running: Yes
Slave_IO_Running ：一個負責與主機的 io 通信
Slave_SQL_Running：負責自己的 slave mysql 進程
測試：數(shù)據(jù)同步
xuexi63 寫數(shù)據(jù)：
mysql> use tree;
Database changed
mysql> show tables;
+--------------+
| Tables_in_tree |
+--------------+
| test1 |
+--------------+
1 row in set (0.00 sec)
mysql> insert into test1 values(1);
xuexi64讀數(shù)據(jù)：
mysql> use tree;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changedmysql> select * from test1;
+------+
| id |
+------+
| 1 |
+------+
排錯：
如果遇到主從不同步，看一下主從 bin-log 的位置，然后再同步。
同步之前如果懷疑主從數(shù)據(jù)不同步可以采?。荷厦胬鋫浞葸h程拷貝法或者在從服務器上命行同步方法。
主服務器：
[root@xuexi63 mysql]# mysqldump -u root -p -B book > v4.sql
加-B，執(zhí)行時，會創(chuàng)建 book 庫：
或
[root@xuexi63 mysql]# mysqldump -u root -p --all-databases > v5.sql
[root@xuexi63 mysql]# scp v5.sql 192.168.1.64:/root
查看 binlog 狀態(tài)：
mysql> show master status ;
+---------- -------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------- ------+----------+--------------+------------------+
| mysqllog.000004 | 106 | tree1 | |
+-----------------+----------+--------------+------------------+
從服務器執(zhí)行
[root@xuexi64 ~]# mysql -u root -p < v5.sql #導入數(shù)據(jù)庫
從服務器執(zhí)行 MySQL 命令下：
mysql> slave stop； #先停止 slave 服務
mysql> change master to master_log_file='mysqllog.000004' ,master_log_pos=106;
[#]根據(jù)上面主服務器的 show master status 的結果，進行從服務器的二進制數(shù)據(jù)庫記錄回歸，達到同步的效果
mysql>slave start; #啟動從服務器同步服務
mysql> show slave status\G;
用 show slave status\G;看一下從服務器的同步情況
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果都是 yes，那代表已經(jīng)在同步重啟從服務器，再查看狀態(tài)：
停止從服務器 slave stop;
開啟從服務器 slave start;
如果出錯：
1、二進制日志沒有開啟
2、IPTABLES 沒有放開端口
3、對應的主機 IP 地址寫錯了
SQL 線程出錯
1、主從服務器數(shù)據(jù)庫結構不統(tǒng)一
出錯后，數(shù)據(jù)少，可以手動解決創(chuàng)建插入，再更新 slave 狀態(tài)。
注：如果主上誤刪除了。那么從上也就誤刪除了。 #因此主上要定期做 mysqldump 備份。
實戰(zhàn) 2：mysql 主主 雙向主從復制：
通過 mysql 主主：進行 mysql 雙向同步數(shù)據(jù)庫 tree1 的配置
mysql 主：服務端：xuexi63.cn IP：192.168.1.63
mysql 主：服務端：xuexi64.cn IP：192.168.1.64
配置 xuexi64 ： 身份 1： xuexi64 的主。 身份 2： xuexi63 的從。
[root@xuexi64 ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
[#] Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=2
master-host=192.168.1.63
master-user=slave
master-password=123456
|####to master
log-bin=mysqlslave-bin-log
binlog-do-db=treebinlog-ignore-db=mysql #避免同步 mysql 用戶 相關配置。
授權：
[root@xuexi64 opt]# mysql -u root -p123456
mysql> grant replication slave on *.* to slave64@'192.168.1.63' identified by '123456';
[root@xuexi64 opt]# service mysqld restart
配置 xuexi63 ： 身份 1： xuexi64 的主。 身份 2： xuexi64 的從。
[root@xuexi63 mysql]# vim /etc/my.cnf
[root@xuexi63 mysql]# cat !$
cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysqllog
server-id=1
binlog-do-db=tree
### to xuexi64 slave mysql
master-host=192.168.1.64
master-user=slave64
master-password=123456
replicate-do-db=tree
[root@xuexi63 ~]# service mysqld restart
測試：
xuexi64
mysql> show master status ;
+---------------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------------+----------+--------------+------------------+
| mysqlslave-bin-log.000001 | 106 | tree | mysql |
+---------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
[#]說明 xuexi64 作為 mysql 主已經(jīng)成功。
xuexi63
[root@xuexi63 mysql]# service mysqld restartStopping mysqld: [ OK ]
Starting mysqld: [ OK ]
mysql> show slave status \G;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 4
Current database: tree
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.64
Master_User: slave64
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqlslave-bin-log.000001
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 260
Relay_Master_Log_File: mysqlslave-bin-log.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: tree
Replicate_Ignore_DB:
測試主主數(shù)據(jù)同步：
xuexi64
mysql> show master status ;
+---------------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------------+----------+--------------+------------------+
| mysqlslave-bin-log.000001 | 106 | tree | mysql |
+---------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
說明 xuexi64 作為 mysql 主已經(jīng)成功。
xuexi63
[root@xuexi63 mysql]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]mysql> show slave status \G;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 4
Current database: tree
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.64
Master_User: slave64
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqlslave-bin-log.000001
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 260
Relay_Master_Log_File: mysqlslave-bin-log.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: tree
Replicate_Ignore_DB:
測試主主數(shù)據(jù)同步：
在 xuexi63 上插入數(shù)據(jù)：
mysql> use tree;
Database changed
mysql> insert into test1 values(66);
Query OK, 1 row affected (0.00 sec)
在 xuexi64 上插入數(shù)據(jù)：
mysql> use tree;
Database changed
mysql> insert into test1 values(77);
在 xuexi63 上查看數(shù)據(jù)：
mysql> select * from test1;
+------+
| id |
+------+
| 1 |
| 2 || 3 |
| 4 |
| 5 |
| 66 |
| 77 |
+------+
在 xuexi64 上查看數(shù)據(jù)：
mysql> select * from test1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 66 |
| 77 |
+------+
總結：說明主主配置成功。
擴展:
mysql 主從配置常見參數(shù)：
root@xuexi64 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
[#] Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[#]添加以下內(nèi)容：
server-id = 2 從服務器 ID 號，不要和主 ID 相同
master-host = 192.168.1.63 指定主服務器 IP 地址
master-user = slave 制定在主服務器上可以進行同步的用戶名
master-password = 123456 密碼master-port = 3306 同步所用的端口，可以不寫
master-connect-retry=60 斷點重新連接時間，可以不寫
replicate-ignore-db=mysql 屏蔽從服務器對 mysql 庫的同步
replicate-do-db=tree1 指定從服務器要同步數(shù)據(jù)庫的名稱。 #不寫也可以。
log-bin=mysqlslave-bin-log 設定生成的二進制文件 log 文件名
binlog-do-db=db1 設置主服務器要同步數(shù)據(jù)庫名。即指定 xuexi64 上要產(chǎn)生的二
進制日志的數(shù)據(jù)庫名。 #這個是主服務器上配置
binlog-ignore-db=mysql 避免同步 mysql 用戶配置，以免不必要的麻煩。#這個是主服務
器上配置
