Backups
The mysqldump command creates a file of SQL statements that when run, will recreate the same tables and data that are in the database. It can be used as a method of backup or as an easy way to copy a database from one server to another. It can also create output as comma separated values (CSV), or even in XML. Since the resulting output is a text file of SQL statements, administrators have the flexibility of viewing and altering the dump file. To dump a single table in a database, use mysqldump as follows: Where “testdb” is the name of the database and “tablename” is the name of the table you wish to dump. The “-u” option specifies which MySQL user to use during the dump and “-p” tells mysqldump to get the password interactively (i.e. you will need to type in the password when prompted). The user needs to have read access to the database that is being dumped (technically the user needs the SELECT, SHOW VIEW, TRIGGER and LOCK TABLES privileges). The “root” user is normally configured (along with a password) during the MySQL server installation.
The output will be sent directly to the console. To send the output to a file, and to specify the password on the command line (useful for backup scripts) then use: Where “secret” is the password for the user root, and tablename is the name of the table you want to back up. You should replace “table” in the filename with the real table name. The resulting file “testdb_table_backup.sql” will contain all the SQL statements necessary to recreate the table. You can supply more than one table parameter to back up several tables, or if you omit it completely, then the entire database will be dumped. For example: The resulting file “testdb_backup.sql” will contain a dump of all the tables in the database. To dump more than one database you need to use the –databases option. For example: To backup all the tables from all the databases on the MySQL server, use the “–all-databases” option:
Admin
The mysqladmin tool is used to perform administrative tasks including checking the server’s configuration and current status, and to create and drop databases, etc. To create a database called “testdb” use: The “-u” and “-p” options work the same as with the mysqldump command. To delete a database along with all of its tables use the “drop” sub-command: After you confirm the deletion of the database, it, along with all its tables, will be removed from the server.
The mysqadmin command can also retrieve various statistics from the MySQL server. Try the “status” and “extended-status” sub-commands. The simplest status request is the “ping” command which checks to see if the server is alive. Here is an example of how to use it: You can get detailed information about the mysqldump command from the MySQL documentation page A Database Backup Program, and likewise for the mysqladmin command from the Client for Administering a MySQL Server page. If you have any questions regarding the examples given above, please ask in the comments section below.
