• Advertisement
Stay in with the IT Notebook

Any - MySQL 3.x+ - Exporting and importing databases

Any software title released with a GNU/Linux distribution. Articles are posted in "Linux Distribution - Software Title - Subject" format.

Any - MySQL 3.x+ - Exporting and importing databases

Postby lmmtux » Thu Aug 30, 2012 12:55 pm

There are a few different ways to backup, copy and move databases with MySQL Server. The method here works the best in most cases and offers a complete solution to back up a database, or to copy/move it to another server. The methods here are done with the "mysql" and "mysqldump" command line tools, which is commonly installed on the MySQL server itself for local administration.


Exporting databases:

  1. Run this command to export the specified database to a .sql file:
    Code: Select all
    mysqldump -h <yourmachine> -u <user> <databasename> > dbdump.sql

    Where:
    • "<yourmachine>" is your SQL server name (it can be "localhost" or a remote system).
    • <user> is a specified user that has permissions to access the database to be exported.
    • <databasename> is the database to export.
    • "dbdump.sql" is the filename of the .sql file to generate.


Importing databases:

  1. Run these commands to import a database saved to a .sql file:
  2. If the destination database does not exist yet, create an empty database:
    Code: Select all
    mysql -u <user> -p
    create database <databasename> ;
    quit

    Where:
    • <databasename> is your database name to create.
    • <user> is the username that has access to the empty database. Typically, you can use the MySQL admin "root" account.
  3. Import the .sql file into the empty destination database:
    Code: Select all
    mysql -u <user> <databasename> < dbdump.sql

    Where:
    • <user> is the username that has access to the empty database. Typically, you can use the MySQL admin "root" account.
    • <databasename> is the empty database name.
    • "dbdump.sql" is the file containing the exported database.
  4. Grant permissions to the new database:
    • To grant permissions and create a new SQL user:
      Code: Select all
      mysql -u <user> -p
      \u <databasename>
      GRANT ALL PRIVILEGES ON <databasename> TO <sqluser>@localhost IDENTIFIED BY 'password' ;
      GRANT ALL PRIVILEGES ON <databasename>.* TO <sqluser>@localhost ;
      FLUSH PRIVILEGES ;
      quit

      Where:
      • <user> is the username that has access to the empty database. Typically, you can use the MySQL admin "root" account.
      • <databasename> is the name of the database.
      • <sqluser> is the local SQL user to create and grant access to. This SQL account will be created with the password specified after the "IDENTIFIED BY" operator.
      • "localhost" can be the local server you are connected to, or a remote MySQL server.
    • To grant permissions to an already existing SQL user:
      Code: Select all
      mysql -u <user> -p
      \u <databasename>
      GRANT ALL PRIVILEGES ON <databasename> TO <sqluser>@localhost ;
      GRANT ALL PRIVILEGES ON <databasename>.* TO <sqluser>@localhost ;
      FLUSH PRIVILEGES ;
      quit

      Where:
      • <user> is the username that has access to the empty database. Typically, you can use the MySQL admin "root" account.
      • <databasename> is the name of the database.
      • <sqluser> is the local SQL user to grant access to.
      • "localhost" can be the local server you are connected to, or a remote MySQL server.
lmmtux
 
Posts: 55
Joined: Mon Jul 30, 2012 9:40 pm
Reputation: 0

Return to Linux

cron