#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    63
    Rep Power
    6

    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)

    Code:
    $query = "id int auto_increment, ";
      $query.= "year char(4), ";
      $query.= " primary key(id)";
    
      $sql = "create table $mynewfile($query)";
    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

    Code:
    $sqlcr = "create table ".$filetouse." select * from  ".$mynewfile ;
    This worked fine and produced a file $filetouse that I could subsequently restore if necessary.

    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.

    Code:
    $sql = "alter table ".$filetouse." modify id int unique auto_increment";
    However, I would appreciate it please if someone can explain why the backup loses the attribute for the primary field?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by rps
    However, I would appreciate it please if someone can explain why the backup loses the attribute for the primary field?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    63
    Rep Power
    6
    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)

    Code:
    mysqldump -u root -ptmppassword dbname tbl_name\ > tbl_name_backup
    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)?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by rps
    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)?
    sorry, i can't help you with command line stuff, because i use a gui (heidisql)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Originally Posted by rps
    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)?
    Normally you backup the entire database (all tables) by omitting the table name:

    Code:
    mysqldump -u root -ptmppassword dbname > db_name_backup.sql
    That way you restore the entire database when something goes wrong.

    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.
    /Stefan
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    63
    Rep Power
    6
    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.

IMN logo majestic logo threadwatch logo seochat tools logo