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以上的表,我目前见到过最大为６０多Ｇ的单表，对于这种表每天一个全备可以说是一件很痛苦的事。
那么有没有办法，可以实现一个全备加增量的备份呢。
答案当然是有的。
在常规环境直可以用全备加binlog一同保存。
这种环境大多可以用一个Slave上进行备份操作。
思路：
先停止Slave的同步，刷新buffer，对于Innodb 如果想直接拷贝还需要把innodb_max_dirty_pages_pct这个值置为零，然后在执行一次flush tables;
就可以cp了。如果是Dump出来可以这这样做。
这个方案目前来看也是比较完美的，但一个并发力度大的应用一天的Binlog有可能能达到５０G－６０G，这样的系统开Binlog可以说是对系统的ＩＯ性能及整体性能都有早影响。
另一种方案就是基于表的上数据的罗辑变化进行备份。
主体思想：全备加逻辑备份。
逻辑备份：当有数据插入时，利用触发器同时写入另一个表，当数据更新时，我们同时记录一下，更新后的数据情况到另一个表。
当有删除操作时，只需要记录一下，删除的主建ＩＤ就行。
例子：
要备份的表：
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的操作：
mysql -e “select concat(‘delete from wubx where id=',id,';') from wubx_dd”>>backup_dd.sql
这样利用这些逻辑的备份加是完毕备份恢复到当前恢复点就很容易了。这里不演示。
这个操作最好用一个程序完成，当取完罗辑备份后，做一个标记点去清楚备份完的数据，以保证，逻辑记录表里的数据量比较少是正确的。
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1671
14
1428
52
1329
25
1276
29
1256
24
Laravel Introduction Example
Apr 18, 2025 pm 12:45 PM
Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.
MySQL and phpMyAdmin: Core Features and Functions
Apr 22, 2025 am 12:12 AM
MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.
MySQL vs. Other Programming Languages: A Comparison
Apr 19, 2025 am 12:22 AM
Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.
Laravel framework installation method
Apr 18, 2025 pm 12:54 PM
Article summary: This article provides detailed step-by-step instructions to guide readers on how to easily install the Laravel framework. Laravel is a powerful PHP framework that speeds up the development process of web applications. This tutorial covers the installation process from system requirements to configuring databases and setting up routing. By following these steps, readers can quickly and efficiently lay a solid foundation for their Laravel project.
Explain the purpose of foreign keys in MySQL.
Apr 25, 2025 am 12:17 AM
In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.
Compare and contrast MySQL and MariaDB.
Apr 26, 2025 am 12:08 AM
The main difference between MySQL and MariaDB is performance, functionality and license: 1. MySQL is developed by Oracle, and MariaDB is its fork. 2. MariaDB may perform better in high load environments. 3.MariaDB provides more storage engines and functions. 4.MySQL adopts a dual license, and MariaDB is completely open source. The existing infrastructure, performance requirements, functional requirements and license costs should be taken into account when choosing.
What software is better for yi framework? Recommended software for yi framework
Apr 18, 2025 pm 11:03 PM
Abstract of the first paragraph of the article: When choosing software to develop Yi framework applications, multiple factors need to be considered. While native mobile application development tools such as XCode and Android Studio can provide strong control and flexibility, cross-platform frameworks such as React Native and Flutter are becoming increasingly popular with the benefits of being able to deploy to multiple platforms at once. For developers new to mobile development, low-code or no-code platforms such as AppSheet and Glide can quickly and easily build applications. Additionally, cloud service providers such as AWS Amplify and Firebase provide comprehensive tools
SQL vs. MySQL: Clarifying the Relationship Between the Two
Apr 24, 2025 am 12:02 AM
SQL is a standard language for managing relational databases, while MySQL is a database management system that uses SQL. SQL defines ways to interact with a database, including CRUD operations, while MySQL implements the SQL standard and provides additional features such as stored procedures and triggers.
