November 14th, 2013, 05:16 AM
Bakup of MySQL table
Can someone explain/clarify please. I created a table with some 200 fields including one named "year" and one named "id". "id" is described as an integer and auto increment and is the primary (unique) key. Essentially the code is (I have omitted the long list of fields)
When created (in 2013) the table had a finite fixed number of records and visitors to the web site populated the fields in this table. Before populating the table a backup of the table was made using the command
$query = "id int auto_increment, ";
$query.= "year char(4), ";
$query.= " primary key(id)";
$sql = "create table $mynewfile($query)";
This worked fine and produced a file $filetouse that I could subsequently restore if necessary.
$sqlcr = "create table ".$filetouse." select * from ".$mynewfile ;
However, I have found that the field "id" in the original table ($mynewfile) is no longer auto-incrementing (I found this because I extended the table for 2014 and added some extra records so that visitors in 2014 will update fields for 2014 and not for 2013).
I have found that the problem stems from the backup. When the table is backed up it works okay but loses the attributes of the "id" field so that when it is restored the "id" field in the original table ($mynewfile) no longer has the auto-increment attributes.
I have solved this by adding the following line immediately after creating the backup file.
However, I would appreciate it please if someone can explain why the backup loses the attribute for the primary field?
$sql = "alter table ".$filetouse." modify id int unique auto_increment";
November 14th, 2013, 08:14 AM
because when you SELECT from a table and pull the values of an auto_increment column, there's nothing in those values that carries over the DDL auto_increment information
for backups, you should be using mysqldump
November 14th, 2013, 01:36 PM
Thanks r937. At first I thought that this function backed up the whole database but then found that I could backup a table using (I think)
However, if I pursue this route, how do I restore the table (or more exactly, list the tables in a Form so that I can select the table to restore)?
mysqldump -u root -ptmppassword dbname tbl_name\ > tbl_name_backup
November 14th, 2013, 02:12 PM
sorry, i can't help you with command line stuff, because i use a gui (heidisql)
November 14th, 2013, 02:57 PM
Normally you backup the entire database (all tables) by omitting the table name:
That way you restore the entire database when something goes wrong.
mysqldump -u root -ptmppassword dbname > db_name_backup.sql
The reason why you do this is that usually you have relations between the tables and you need to keep the integrity of the database intact and the only way you can to this is by taking a snapshot of the entire database.
This sql file is a normal textfile so you can just open it (if your editor can handle a large file, in case your database is large) and cut an paste the table that you want.
On a side note I can also mention that your previous backup strategy that didn't store auto_increment information doesn't either store index information, etc, so the table you restore from that is quite contorted from original table design.
November 17th, 2013, 03:35 PM
Thanks everyone. As my table is a simple matrix of approximately 20 rows by 250 columns (for each year) into which users add information I don't have to worry about indexes or cross referencing to other tables. It is stand alone. The only reason that I copy the table is just in cae the user records incorrect information in which case I can restore it. So thank you for your help and advice/information.