MySQL中表的復制以及大型數據表的備份教程

2024-07-24 13:08:12

字體：大中小

來源：轉載

供稿：網友

這篇文章主要介紹了MySQL中表的復制以及大型數據表的備份教程,其中大表備份是采用添加觸發器增量備份的方法,需要的朋友可以參考下

表復制

mysql拷貝表操作我們會常常用到，下面就為您詳細介紹幾種mysql拷貝表的方式，希望對您學習mysql拷貝表方面能夠有所幫助。

假如我們有以下這樣一個表：

id username password

-----------------------------------

1 admin *************

2 sameer *************

3 stewart *************

CREATE TABLE IF NOT EXISTS `admin` (

`id` int(6) unsigned NOT NULL auto_increment,

`username` varchar(50) NOT NULL default '',

`password` varchar(100) default NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

1. 下面這個語句會拷貝表結構到新表newadmin中。 (不會拷貝表中的數據)

CREATE TABLE newadmin LIKE admin

2. 下面這個語句會拷貝數據到新表中。注意：這個語句其實只是把select語句的結果建一個表。所以newadmin這個表不會有主鍵，索引。

CREATE TABLE newadmin AS

(

SELECT *

FROM admin

)

3. 如果你要真正的復制一個表。可以用下面的語句。

CREATE TABLE newadmin LIKE admin;

INSERT INTO newadmin SELECT * FROM admin;

4. 我們可以操作不同的數據庫。

CREATE TABLE newadmin LIKE shop.admin;

CREATE TABLE newshop.newadmin LIKE shop.admin;

5. 我們也可以拷貝一個表中其中的一些字段。

CREATE TABLE newadmin AS

(

SELECT username, password FROM admin

)

6. 我們也可以講新建的表的字段改名。

CREATE TABLE newadmin AS

(

SELECT id, username AS uname, password AS pass FROM admin

)

7. 我們也可以拷貝一部分數據。

CREATE TABLE newadmin AS

(

SELECT * FROM admin WHERE LEFT(username,1) = 's'

)

8. 我們也可以在創建表的同時定義表中的字段信息。

CREATE TABLE newadmin

(

id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY

)

AS

(

SELECT * FROM admin

)

MySQL大表備份

這里所說的大表是超過4G以上的表,我目前見到過最大為60多G的單表，對于這種表每天一個全備可以說是一件很痛苦的事。

那么有沒有辦法，可以實現一個全備加增量的備份呢。

答案當然是有的。

在常規環境直可以用全備加binlog一同保存。

這種環境大多可以用一個Slave上進行備份操作。

思路：

先停止Slave的同步，刷新buffer，對于Innodb 如果想直接拷貝還需要把innodb_max_dirty_pages_pct這個值置為零，然后在執行一次flush tables;

就可以cp了。如果是Dump出來可以這這樣做。

這個方案目前來看也是比較完美的，但一個并發力度大的應用一天的Binlog有可能能達到50G-60G，這樣的系統開Binlog可以說是對系統的IO性能及整體性能都有早影響。

另一種方案就是基于表的上數據的羅輯變化進行備份。

主體思想：全備加邏輯備份。

邏輯備份：當有數據插入時，利用觸發器同時寫入另一個表，當數據更新時，我們同時記錄一下，更新后的數據情況到另一個表。

當有刪除操作時，只需要記錄一下，刪除的主建ID就行。

例子：

要備份的表：

CREATE TABLE `wubx` ( `id` int(11) NOT NULL auto_increment,

`user_id` int(11) NOT NULL default '0',

`friend_id` int(11) NOT NULL default '0',

`dir_id` int(11) NOT NULL default '0',

`created` int(11) NOT NULL default '0',

UNIQUE KEY `id` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

對于這個表我們需要建一個記錄有新數據變化的表為：

mysql> create table wubx_ii like wubx;

Query OK, 0 rows affected (0.00 sec)

mysql> create table wubx_uu like wubx;

Query OK, 0 rows affected (0.00 sec)

mysql> create table wubx_dd ( id int(11));

Query OK, 0 rows affected (0.00 sec)

建立相應的觸發程器

記錄insert的操作：

delimiter //

create trigger wubx_ii after insert on wubx for each row begin insert into wubx_ii set id=new.id,user_id=new.user_id,friend_id=new.friend_id,dir_id=new.dir_id,created=new.created; end//

記錄update的操作：

create trigger wubx_uu after update on wubx for each row begin replace into wubx_uu set id=new.id,user_id=new.user_id,friend_id=new.friend_id,dir_id=new.dir_id,created=new.created; end//

記錄刪除的操作：

create trigger wubx_dd after delete on wubx for each row begin insert into wubx_dd values(old.id); end//

delimiter ;

操作：

先備份原始表wubx里的數據：

進行：

insert into wubx values(”,1,10,1,1198464252);

insert into wubx values(”,1,11,1,1198464252);

insert into wubx values(”,1,2,1,1198464252);

insert into wubx values(”,2,10,1,1198464252);

insert into wubx values(”,2,12,1,1198464252);

insert into wubx values(”,3,12,1,1198464252);

update wubx set dir_id=5 where user_id=3;

update wubx set dir_id=4 where user_id=3;

delete from wubx where user_id=2 and friend_id=12;

現在要實現增量備份：

取出insert的操作：

mysql -e ” select concat(‘replace into wubx set id=',id,',user_id=',user_id,',friend_id=',friend_id,',dir_id=',dir_id,',created=',created,';') from wubx_ii;”>>backup_ii.sql

取出update的操作：

mysql -e ” select concat(‘update wubx set user_id=',user_id,',friend_id=',friend_id,',dir_id=',dir_id,',created=',created,' where id=',id,';') from wubx_uu;”>>backup_uu.sql

取出delete的操作：