Backup a table in MySQL

In my experiment, each experiment running needs a table in the control machine to record the global inserted files in the system. When an experiment finished, a new empty table should be created in order to record the new experiment's inserted files. For later experiment process, the old table should be backed up also. There could be more efficient and reliable way to back up the tables in a MySQL database, but here is my method to accomplish this task with two SQL commands:

1. Rename the old table: RENAME TABLE store_record TO <new_name>
2. Create an empty table: CREATE TABLE <new_name> LIKE store_record

 After these two SQL command, the original 'store_record' table becomes 'new_name' table and an empty new 'store_record' table is created.
WARNING: there is no quotation marks for table name!

This is put in the process of cleaning up experiment results. If cleaning up is necessary, then the data in 'store_record' table is useless. And it is free to be replaced by an empty one for next experiment. If the cleaning up is not performed, it means that the data in last experiment is useful in the next experiment, then the remaining of data in 'store_record' is meaningful.

Install MySQL and Development environment in Fedora 17

The installation of MySQL in Fedora is easy and trivial task because the MySQL server package is available in official yum repository. It can be installed both by yum and GUI installer.

The most important task is to configure the MySQL into a usable basic status. Because the Fedora 16/17 introduced another system service manipulating program, "systemctl", which is recommended by Fedora 16/17 in their deployment documentations, the configuration procedure is a little different from the previous Fedora systems that use "service" and "chkconfig" commands. These old programs, however, are still accessible in Fedora 16/17. Here is an example of "systemctl" usage:

systemctl status mysqld.service

Notice that the service name is followed by a word "service".


Enabling a service to start automatically at boot time:

systemctl enable mysqld.service

Disabling a service to start at boot time:

systemctl disable mysqld.service

Checking the service status:

systemctl status mysqld.service

Running, Stoping and Restarting a service:

systemctl [start | stop | restart] mysqld.service

After the MySQL service is started and is added to the start queue at boot time, several privilege settings should be done. There are many methods to accomplish this task. I'd like to use the provided program: "mysql_secure_installation". When this step is completed, the password for root is changed, and the anonymous account and test database should be properly set as desired.


In order to access the MySQL from remote machines, do not forget to open the port 3306 for the MySQL server.

More configurations and database set up can be done by using the GUI workbench of MySQL.

Add column to existing table

Many ways to add column to existing table in MySQL. If you r using GUI tools, add column to table in MySQL is easy. If you are using CLI, then here’s the command to add column in MySQL table.

To add column in existing table, you may refer to the 2 examples below:-
Example I: Add new varchar column to the end of the table

ALTER TABLE `tablename_here` ADD `new_column_name` VARCHAR( 255 ) NOT NULL ;

Example II: Add new integer column after an existing column in table

ALTER TABLE `tablename_here` ADD `new_column_name` INT NOT NULL AFTER `existing_column` ;

It’s simple to add column to existing table right? 

reference: http://www.techiecorner.com/560/mysql-how-to-add-column-to-existing-table/

Change table properties in MySQL::

The most basic one is :


ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;



More detailed can be found here:

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

Experiment Q&A............

When I compiled the lookup program using MySQL connector C++ statically:

Q: what if I am confronted with "ld: can not found lm "?

A: The reason is that 'ld' can not find the static version of 'libm.a' within the default position.

     So I need to install it in Fedora using yum: "sudo yum install glibc-static". This should solve the 'lm' problem.

Q: what if I am confronted with message: "undefined reference to 'pthread_self' and so on...."?

A: The problem is that the pthread library can not be found. Just direct the g++ the location of it by append "-lpthread" at the end.

Q: What is the command that I used to compile the program and link against the static library?

A: Like this: "g++ -static -o <dst> <src> /usr/local/lib/libmysqlcppconn-static.a /usr/local/mysql/lib/libmysqlclient.a -lpthread".

Q: When using the static library, sometimes there is a strange message pop out: can not found lc ?
A: Because the static version of glibc is not installed by default, we need to install by ourself:
        sudo yum install glibc-static

DELETE all of rows in a TABLE in MySQL (Refer to others' work)

Delete and Truncate

There are two ways to delete all the data in a MySQL database table.

TRUNCATE TABLE tablename; This will delete all data in the table very quickly. In MySQL the table is actually dropped and recreated, hence the speed of the query. The number of deleted rows for MyISAM tables returned is zero; for INNODB it returns the actual number deleted.

DELETE FROM tablename; This also deletes all the data in the table, but is not as quick as using the "TRUNCATE TABLE" method. In MySQL >= 4.0 the number of rows deleted is returned; in MySQL 3.23 the number returned is always zero.

Auto Increment Columns for MyISAM Tables

If you have an auto increment primary key column in your MyISAM table the result will be slightly different depending which delete method you use. When using the "TRUNCATE TABLE" method the auto increment seed value will be reset back to 1. When using the "DELETE FROM" method the auto increment seed will be left as it was before (eg if the auto increment field of last inserted record was 123 the next inserted record will be set to 124).

Note that this is true for MySQL >= 4.0; from my reading of the TRUNCATE manual page in MySQL 3.23 TRUNCATE works just like DELETE which would mean the auto increment seed is not reset. I do not currently have a 3.23 database set up to test it so cannot confirm this.

Auto Increment Columns for INNODB Tables

For INNODB tables, whether you use the "TRUNCATE TABLE" or "DELETE FROM" methods, the auto increment field will not be reset. If you inserted 5 records into a new table, then deleted all records and inserted another record, the field would have a value of 6, regardless of which method you used.

Update 17 Feb 2009: I originally wrote this post when MySQL 4.0 was the current version. I've just tested the above now on an INNODB table using MySQL 5.0 and using TRUNCATE does reset the auto increment field back to the default. So either the behaviour changed at some point or I was incorrect when making the above statement.

Are you really sure you want to delete all data?

Before deleting all the data in a database you should make sure you really intend to delete all the data. It often pays first to "SELECT * FROM tablename" or "SELECT COUNT(*) FROM tablename" before doing so to check that it really is safe to delete all data. Maybe you really want to do something like "DELETE FROM tablename WHERE foo = 'bar'" instead.

Reference: http://www.electrictoolbox.com/article/mysql/delete-all-data-mysql/