MySql Database Backup in CentOS Commandline

[root@admission]# cd
[root@admission /]# mysqldump -uroot -p admissio_kuet > backupfile.sql
Enter password:
[root@admission /]#
(backup success !)

Creating A Backup

The mysqldump command is used to create textfile “dumps” of databases managed by MySQL. These dumps are just files with all the SQL commands needed to recreate the database from scratch.

If you want to back up a single database, you merely create the dump and send the output into a file, like so:

mysqldump database_name > database_name.sql

Multiple databases can be backed up at the same time:

mysqldump --databases database_one database_two > two_databases.sql

It is also simple to back up all of the databases on a server:

mysqldump --all-databases > all_databases.sql

Restoring a Backup

Since the dump files are just SQL commands, you can restore the database backup by telling mysql to run the commands in it and put the data into the proper database.

mysql database_name < database_name.sql

If you are trying to restore a single database from dump of all the databases, you have to let mysql know like this:

mysql --one-database database_name < all_databases.sql

Common Useful MySQL Commands

Applicable to Centos Versions:

  • Centos 5.x
  • Centos 6.x

Requirements

Explanation of requirements.

  1. MySQL and MySQL-server installed on a Centos 5.x system
  2. DBA access rights to MySQL on a Centos 5.x system

Doing the Work

Basic description of what will be done and what is expected.

    1. Database file locations:
In Centos 5.x the location where the raw uncompressed MySQL databases are stored is: /var/lib/mysql
    1. Users and DBA’s (Database Admins) create, delete, manipulate:
Note: To create, delete or alter a DBA you'll first need to be logged in as the root mysql DBA or a DBA with full access.

Show all users in the mysql database:
mysql> SELECT * FROM mysql.user;

Delete a null user:
mysql> DELETE FROM mysql.user WHERE user = ' ';

Delete all users who are not root:
mysql> DELETE FROM mysql.user WHERE NOT (host="localhost" AND user="root");

Change root user to a less-guessable name (for security):
mysql> UPDATE mysql.user SET user="mydbadmin" WHERE user="root";

Create a new DBA:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;

Add a new user with specific privileges on a specific database:
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON mydatabase.* TO 'username'@'localhost' IDENTIFIED BY 'mypass';

Add a new DBA with all privileges to a specific database:
mysql> GRANT ALL PRIVILEGES ON mydatabase.* TO 'username'@'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;

Change a user or DBA password:
mysql> UPDATE mysql.user SET password=oldpass("newpass") WHERE User='username';

Delete a user or DBA from the database:
mysql> DELETE FROM mysql.user WHERE user="username";
    1. Simple database manipulation:
Show all databases:
mysql> SHOW DATABASES;

Create a database:
mysql> CREATE DATABASE mydatabase;

Use a specific database:
mysql> USE mydatabase;

Delete a database:
mysql> DROP DATABASE mydatabase;

Optimize a database:
All Databases:
$ sudo mysqlcheck -o --all-databases -u root -p
Single Database:
$ sudo mysqlcheck -o db_schema_name -u root -p

Rename a database from command line (not from within MySQL), there is purposely no space beween -p and mypass:

mysqldump -u dbauser -pmypass databasename > mybackup.sql

mysql -u dbauser -pmypass newdatabasename < mybackup.sql

Show the last 200 queries to your database with the sample table name "queries" and the sample field "query_id":

mysql> SELECT * FROM queries ORDER BY query_id DESC LIMIT 200;
    1. Simple table manipulation:
Show all tables within a previously selected database 
mysql> SHOW TABLES;

Show data within a specific table in a previously selected database:
mysql> SELECT * FROM tablename;

Rename a table within a specific database:
mysql> RENAME TABLE first TO second;
or
mysql> ALTER TABLE mytable rename as mynewtable;

Drop a table from a specific database:
mysql> DROP TABLE mytable;
    1. Simple column and field manipulation:
Show columns within a table:
mysql> DESC mytable;
or
mysql> SHOW COLUMNS FROM mytable;

Update a field within a previously selected database table:
mysql> UPDATE mytable SET mycolumn="newinfo" WHERE mycolumn="oldinfo";

Select data within a specific table in a previously selected database:
mysql> SELECT * FROM mytable WHERE mycolumn='mydata' ORDER BY mycolumn2;

Insert data into a table and its columns within a specific database:
mysql> INSERT INTO mytable VALUES('column1data','column2data','column3data','column4data','column5data','column6data','column7data','column8data','column9data');

Delete data from specific fields within a column:
mysql> DELETE FROM mytable WHERE mycolumn="mydata";

Update data in a specific table, column, field:
mysql> UPDATE mytable SET column1="mydata" WHERE column2="mydata";
    1. Simple backup options:
Backup all databases uncompressed from the command line (not from within MySQL):
backup: mysqldump -u root -pmypass --all-databases > alldatabases.sql

restore full: mysql -u username -pmypass < alldatabases.sql (no space in between -p and mypass)

restore single: mysql -u username -pmypass mydb < mydb.sql (no space in between -p and mypass)

Backup all databases compressed from the command line (not from within MySQL):
With bzip2: mysqldump --all-databases | bzip2 -c > databasebackup.sql.bz2 (use bunzip2 to uncompress)

With gzip: mysqldump --all-databases | gzip > databasebackup.sql.gz (use gunzip to uncompress)

Mini script to backup all databases and tables using gzip:
#!/bin/sh
date=`date -I`
mysqldump --all-databases | gzip > /var/backup/dbbackup-$date.sql.gz

Backup a specific database only:
mysqldump -u username -pmypass databasename > backupfile.sql

Backup database structure only:
mysqldump --no-data --databases databasename > structurebackup.sql


Backup a specific database and specific tables within that database only:
mysqldump --add-drop-table -u username -pmypass databasename table_1 table_2 > databasebackup.sql

The syntax for the command to issue is:
mysqldump -u [username] -p[password] [databasename] [table1 table2 ....] > backupfilename.sql

Backing up only the database structure of specific databases, not the actual data:
mysqldump --no-data --databases db1 db2 db3 > structurebackup.sql
    1. Improve local and remote access security:
Disable the use of LOAD DATA LOCAL INFILE command, which will help to prevent against unauthorized reading from local files. This matters especially when new SQL Injection vulnerabilities in PHP applications are found. This can be set to 1 temporarily for a local admin to import a csv file into the database and then turned off again as well. The mysqld service will need to be restarted after each change.

For that purpose, the following parameter should be added in the [mysqld] section in /etc/my.cnf:

set-variable=local-infile=0

_____________________


The first change applies to the 3306/tcp port, on which MySQL listens by default. Because, according to the initial assumptions, the database will be used only by locally installed PHP applications, we can freely disable listening on that port. This will limit possibilities of attacking the MySQL database by direct TCP/IP connections from other hosts. Local communication will be still possible throw the mysql.sock socket.

In order to disable listening on the mentioned port, the following parameter should be added to the [mysqld] section of /etc/my.cnf:

skip-networking


SSH Tunneling can be used for remote backup scripts which require access to the machine.

Troubleshooting

How to test

Explanation of troubleshooting basics and expectations.

    1. Make sure mysql and mysql-server are installed:
rpm -q mysql mysql-server
    1. Set mysqld to start on boot in runlevels 2,3,4, and 5, start the service and verify it’s running (you’ll need root privileges for this):
chkconfig --level 2345 mysqld on; service mysqld start; service mysqld status

Common problems and fixes

Common problems described here, includes links to known common problems if located on another site

More Information

Any additional information or notes.

Disclaimer

We test this stuff on our own machines, really we do. But you may run into problems, if you do, come to #centoshelp on irc.freenode.net

Added Reading

http://centoshelp.org/servers/database/common-useful-mysql-commands/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s