- Wikipedia: MySQL
- 4.5.1 mysql — The MySQL Command-Line Tool
- Episode #10 - Openfire XMPP/Jabber on CentOS
- Episode #17 - Why you should use a Wiki
- Episode #21 - Anatomy of a SQL Injection Attack leading to Code Execution
- 4.4.4 mysql_secure_installation – Improve MySQL Installation Security
- 5.1.3 Server Command Options - bind_address
- 5.2.3 The General Query Log
- Sams Teach Yourself SQL in 24 Hours (5th Edition)
- 13.7.1.2 CREATE USER Syntax
- 13.7.1.4 GRANT Syntax
- 4.5.4 mysqldump — A Database Backup Program
- Percona XtraBackup
- MySQL Performance Blog
- High Performance MySQL: Optimization, Backups, and Replication
- Episode #25 - Bits Sysadmins Should Know - Technical Books
- phpMyAdmin
In this episode, we are going to cover common tasks associated with looking after a single MySQL server. We will discuss installing and securing the server, useful debugging techniques, common SQL statements, user access and permissions, and finally backup and restore options.
This episode is a longer than I normally make, but hopefully it can serve as a crash course on how to use, and configure a fairly secure MySQL server. In this episode, I really wanted to highlight the why, rather than just the how. For example, why you should add user accounts to the database server, rather than just telling you too, without explaining the reasoning behind it.
As the wiki page mentions here, you can either pronounce it as My-S-Q-L or My-Sequel, although, I prefer My-S-Q-L. MySQL is an extremely popular open-source database and it is a cornerstone of the LAMP stack. For this reason, as a sysadmin, you are likely to find yourself looking after one. So, I thought I would cover some useful info that I have used over the years, this will be pretty basic bits needed for hosting, say a Drupal site, Rails app, or a PHP app. The following with be fairly introductory, but should be sufficient to get you going, as I do not think the average person is not going to use replication, or very complex setups requiring lots of tuning, but if there is need, I will cover these topics in a later episodes.
Lets get the ball rolling by first installing a MySQL server. I would like to mention, that what we are going to cover today should work across Linux distributions, since it is application specific. For a point of references, today we are going to be using a CentOS 6.5 virtual machine with the latest updates. The only real difference between Ubuntu and CentOS should be the commands used to install the mysql-server. For example, on Ubuntu, to install the MySQL server, you would run, apt-get install mysql-server, whereas on CentOS, you run yum install mysql-server. If you would like to see episodes targeting a specific distribution please let me know, or maybe we can figure out a way to highlight the differences between them in a single episode.
$ cat /etc/redhat-release
$ yum update
$ apt-get install mysql-server
$ yum install mysql-server
Okay, now that we have the MySQL server installed, lets fire it up, by running, service mysqld start. You will see there is plenty of output because this it our first time running the MySQL server. Lets just scroll up and we can walk through what this means.
$ service mysqld start
So, MySQL is initializing the mysql databases and populating tables, these are used for system level things, like user access, etc. The server typically eats its own dog food, in that it will use the MySQL server, to store bits about databases, users, permissions inside itself. We do not need to worry about this next bit, as init scripts are populated on CentOS and Ubuntu.
This next bit is pretty important and talks about setting the databases root password. This can be a bit confusing, as there are really two accounts named root. One for the operating system, this has nothing to do with MySQL, and a MySQL root account, in that it has access to everything on the MySQL server. Just wanted to highlight this because it can be a bit confusing. Since this is the root level account for the MySQL server, it is a good idea to set a strong password. You can do that by running these commands, or using this script to walk you through securing your server. Personally, I prefer the script because it removes anonymous access, and cleans up some test databases. The remainder of the output talks about how to start your MySQL server, which on CentOS and Ubuntu does not really apply, as you will typically use the init scripts.
I am going to focus on security throughout this episode, as MySQL is typically a juicy target, as it hold lots of information, things like email addresses, user names, passwords, and most if not all of the user generated content powering your apps. So, this is something you want to spend some time getting right.
Lets go ahead and run the mysql secure installation script, to walk through securing our server. First, we are asked to provide the current MySQL root password, since this is a fresh install, it has not been set, so it is currently blank. Lets just hit enter here and move into the next step. Next, we are asked it we would like to set a root password, obviously this is a good idea, as it prevents root level access to the MySQL databases. Next we are prompted to enter a password, then re-enter it. Next, we are asked if we would like to remove anonymous access, if this is a production MySQL server, there should be no reason to provide anonymous access, so disable it. Next, we are asked if we want to restrict MySQL root logins to localhost, just so that root cannot login over the network, say for example to prevent a brute force password attack. Next, we are asked if we want to remove the test database which anyone can access, yes, lets delete it. Finally, we are asked if we want to flush the privileges, making them active right now, yes, lets do that.
$ /usr/bin/mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we will need the current
password for the root user. If you have just installed MySQL, and
you have not set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
Set root password? [Y/n]
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n]
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n]
... Success!
By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n]
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n]
... Success!
Cleaning up...
All done! If you have completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!
There are a couple other tweaks that I typically make, which I think make things a little more secure, and easier to debug. In most small scale LAMP stacks, that is Linux, Apache, MySQL, and PHP, from what I have found, these are typically all located on the same machine, then as you scale, you might add more machine, or move the database elsewhere. So, if you are small scale, and running everything on the same machine, I like to make the MySQL server only listen on localhost, as opposed to any network IP address, as there is no need to accept network connections if everything is hosted locally to that machine. Let me show you what I mean by running, netstat -nap|grep 3306. 3306 is the MySQL servers port it accepts connections on, and as you can see here, this 0.0.0.0 basically means, accept tcp connections on any IP address this machine has, for the MySQL server port 3306.
$ netstat -nap|grep 3306 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1653/mysqld
Lets edit the MySQL servers configuration file, and I am just going to add a line here, saying bind-address equals 127.0.0.1. This will make the MySQL server only listen for connections on the local machine, accepting nothing from remote machines. Okay, lets save the file, then restart the mysqld server, by running service mysqld restart. Finally, lets verify this works as expected by running, netstat -nap again, and as you can see, we are only listening for connection on 127.0.0.1, or localhost.
$ vi /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 bind-address=127.0.0.1 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
$ service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ]
$ netstat -nap|grep 3306 tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 1886/mysqld
This next tweak, we are going to enable something called the MySQL query log, this basically dumps all SQL statements run by the server into a file, and it can be extremely useful for debugging issues when first setting up your server. We are actually going back into the MySQL server configuration file here. I did not do this in the last step, as I wanted to break these things up, and I typically like to make a change, test it worked, and then move onto the next thing. So, lets go down to the bind-address change we just made a minute ago, actually, lets me just add a comment here, typically I like to make comments, in this case it might be a little redundant, but I think it is good practice. Next, I am just going to copy a couple lines to enable the query log, and then explain what they do. I should mention, that I have added links in the episode notes for all of these configuration tweaks, and much of the supporting material for things talked about here. Okay, so this first line, enables the query log, and the second like, specifies where to put it, in this case, /var/log/query.log, finally lets save the file. So, this is a bit of a personal preference, I like to have all of my logs in /var/log, so it is kind of a one stop shop of log files. However, this creates a problem with the change we just made, in that the MySQL server is running as its own user, so it cannot create files in /var/log, the fix is to create the destination log, and change the user and group ownership over to mysql user. These two commands do this. You could get around this by putting the log in a directory owned by MySQL. Finally, we need to restart mysqld for the change to take effect. I am not going to show you the logs just yet, as I want to show you some other bits that will generate log data, then we can come back to this in a couple minutes and review what it looks like.
$ vi /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # bind to localhost bind-address=127.0.0.1 # enable logging general_log=1 general_log_file=/var/log/query.log [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
$ touch /var/log/query.log $ chown mysql:mysql /var/log/query.log
$ service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ]
Okay, so at this point, we have our MySQL server installed, made some security tweaks, and also enabled the query log to help debugging down the road. The question is, now what? Well, I wanted to cover some common SQL statements, which can be used for exploring the MySQL server and data hosted by it. This will hopefully make the MySQL server less of a black box.
We are mainly going to focus on a command called mysql, lets just review the manual page for it, by running man mysql. As you can see, this is the mysql command line tool, this will allow us to connect to the MySQL server, and issues commands.
$ man mysql
Lets test it out by running, mysql -u, the u stands for the user we want to connect as, in this case root, and -p, to prompt for password. You can actually enter the password following the -p option at the command line too, this will be saved to the bash history, and will also show up in the process listing, so I do not recommend doing that. In stead, if you leave it blank, the mysql command will prompt you for it.
$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.1.73-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Once we are in, we have the mysql prompt, where we use SQL statements to query the MySQL server, to see for example, what databases exists, the tables in each database, and what the data looks like in those tables. I am going to show you how this works at a basic level, but there are plenty of books out there too. For example, I read Teach Yourself SQL in 24 Hours many years ago, I am generally not a huge fan of these 24 hour type books, but this one is pretty good. You can also find it on Safari Books Online, I talked about this service in episode #25, under the technical books section.
Okay, lets head back to the mysql prompt and have a look around. I should mention that all of these commands throughout the episode are listed in the episode notes below, so do not worry about copying any of them down.
First off, you probably want to know what databases exist on the system. To do this, we just type, show databases, and to close off a SQL statement you always use a semicolon. Next, lets say we wanted to look around a database, well we can change into it by running, use, and then the database name, in this case, mysql. To get a listing of the tables in this database, you can type show tables. What about if you wanted to get an idea of what one of these tables looks like? Well, you can type describe, and then the table name, lets choose the host tables. As you can see it outputs the fields and their type. Okay, so that is how to see what databases exist of a server, and how to tell what tables those databases have, and what the tables look like.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ 2 rows in set (0.00 sec)
mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed
mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | | ndb_binlog_index | | plugin | | proc | | procs_priv | | servers | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 23 rows in set (0.00 sec)
mysql> describe host; +-----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | +-----------------------+---------------+------+-----+---------+-------+ 20 rows in set (0.01 sec)
But this will probably make more sense if we just create an example database, along with a table, and populate it with some data. Then we can see what this looks like from the ground up. So, lets create a database called episode38, by running, create database episode38. Now, lets see if that database exists, by running, show databases, again. Cool, looks like it is working, so lets change into that database, by running, use episode38, then get a table listing, by running, show tables.
mysql> create database episode38; Query OK, 1 row affected (0.00 sec)
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | episode38 | | mysql | +--------------------+ 3 rows in set (0.00 sec)
mysql> use episode38; Database changed
Okay, so nothing exists, well lets create a blog table, which will hold our example blog postings. I am just going to paste this in here, as it would take a while to type. So the syntax looks like this, create table blog, then we list the table columns, and their type. Now lets run show tables again, and describe the table too. So far so good. Now, lets populate the table with some example content. Again, I am just going to copy and paste this in, but you can find all of these commands in the episode notes below, say for example that you wanted to try this on your own.
mysql> show tables; Empty set (0.00 sec)
CREATE TABLE `blog` ( `id` int(11) DEFAULT NULL, `content` varchar(100) DEFAULT NULL );
mysql> CREATE TABLE `blog` ( -> `id` int(11) DEFAULT NULL, -> `content` varchar(100) DEFAULT NULL -> ); Query OK, 0 rows affected (0.01 sec)
mysql> show tables; +---------------------+ | Tables_in_episode38 | +---------------------+ | blog | +---------------------+ 1 row in set (0.00 sec)
mysql> describe blog; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | content | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
INSERT INTO `blog` VALUES (1,'first blog posting'); INSERT INTO `blog` VALUES (2,'second example posting'); INSERT INTO `blog` VALUES (3,'final posting');
mysql> INSERT INTO `blog` VALUES (1,'first blog posting'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `blog` VALUES (2,'second example posting'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `blog` VALUES (3,'final posting'); Query OK, 1 row affected (0.00 sec)
Okay, so we have inserted 3 records, into our blog table, in the episode38 database. We can pull all records out of the blog table, by running, select * from blog, or even count the records in a particular table, by running, select count(*) from blog. What about if you wanted to pull a specific record out of the database, based off some type of key? Say for example, that we wanted to pull a record out of the blog table, with an ID, of 1? Well, we can write something like, select * from blog where id = 1. This is in fact how most blog applications work at a basic level. On that note, most authentication systems work in a similar way, by doing something like, select * from blog where email = ‘justin@example.com’. If we run this, we will likely get an error message about a missing email column.
mysql> select * from blog; +------+------------------------+ | id | content | +------+------------------------+ | 1 | first blog posting | | 2 | second example posting | | 3 | final posting | +------+------------------------+ 3 rows in set (0.00 sec)
mysql> select count(*) from blog; +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec)
mysql> select * from blog where id = 1; +------+--------------------+ | id | content | +------+--------------------+ | 1 | first blog posting | +------+--------------------+ 1 row in set (0.00 sec)
mysql> select * from blog where email = 'justin@example.com'; ERROR 1054 (42S22): Unknown column 'email' in 'where clause'
So, that is the basics of exploring the database, how to create databases, and tables, along with inserting data, and pulling records out. I should probably mention that you can delete databases too. You simply need to run, drop database, and the database name, so lets try this out, by deleting our episode38 database. You will notice that we are not prompted or anything, so make sure you know what you are doing, as there is likely no coming back from that, unless you restore from backup. Okay, so this wraps of the basics of exploring the database server, to exit the shell, you can type quit.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | episode38 | | mysql | +--------------------+ 3 rows in set (0.00 sec)
mysql> drop database episode38; Query OK, 1 row affected (0.00 sec)
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ 2 rows in set (0.00 sec)
mysql> quit Bye
So remember a while ago, I was saying that you can debug things in the MySQL server, via log files stored in /var/log, well lets go have a look at those, now that we have some example queries run against our server. Lets change into /var/log and have a look. There are actually two log files in here for MySQL, the first is mysqld.log, and the one we created earlier, query.log. The mysqld.log file contains messages about the MySQL server, things like when it was stopped and started will be logged here. Next, lets have a peek at the query log, as you can see it have a listing of all our SQL commands run against the MySQL server. This can be useful in situations where you have an application issue you are trying to troubleshoot. Say for example, that you have an application that it trying to connect, but seems to fail over and over, what can you do? Well, lets simulate a failure and see what happens. Lets try and connect to the MySQL server using a wrong password, lets type, mysql -u root -p, and then type some random data for the password. So, as you would expect it fails, but lets take a look at the query log too. As you can probably guess, this type of thing can come in really handy for troubleshooting issues. However, once you have things working as you want, I highly suggest you disable this query log, as it is mainly used for debugging. For performance monitoring and improvement, it can also be really handy to log queries, based on the execution time, then you can zero in on long running queries.
$ cd /var/log
$ tail mysqld.log 141003 4:34:22 [Note] /usr/libexec/mysqld: Shutdown complete 141003 04:34:22 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 141003 04:34:22 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 141003 4:34:22 InnoDB: Initializing buffer pool, size = 8.0M 141003 4:34:22 InnoDB: Completed initialization of buffer pool 141003 4:34:22 InnoDB: Started; log sequence number 0 44233 141003 4:34:22 [Note] Event Scheduler: Loaded 0 events 141003 4:34:22 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.1.73-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
$ tail query.log
6 Query INSERT INTO `blog` VALUES (3,'final posting')
141004 7:26:20 6 Query select * from blog
141004 7:26:32 6 Query select count(*) from blog
141004 7:27:55 6 Query select * from blog where id = 1
141004 7:28:45 6 Query select * from blog where email = 'justin@example.com'
141004 7:29:04 6 Query show databases
141004 7:29:21 6 Query drop database episode38
6 Query SELECT DATABASE()
141004 7:29:28 6 Query show databases
141004 7:29:31 6 Quit
$ mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
$ tail query.log 141004 7:26:20 6 Query select * from blog 141004 7:26:32 6 Query select count(*) from blog 141004 7:27:55 6 Query select * from blog where id = 1 141004 7:28:45 6 Query select * from blog where email = 'justin@example.com' 141004 7:29:04 6 Query show databases 141004 7:29:21 6 Query drop database episode38 6 Query SELECT DATABASE() 141004 7:29:28 6 Query show databases 141004 7:29:31 6 Quit 141004 7:44:09 7 Connect Access denied for user 'root'@'localhost' (using password: YES)
I just wanted to cover a couple more points before we finish. We are going to look at creating database role accounts, setting permissions on those accounts, and finally, simple ways to backup and restore databases.
It is good practice to create role accounts for each type of application that is going to access the MySQL database. You can do this through the, create user SQL syntax. Just like you would not hand out the OS root password to everyone, you should not hand out the root MySQL account to apps, as they can do anything on the MySQL database server. Say for example, that someone uses a SQL injection attack against one of your apps, they could potentially, use that access to peer into other databases and steal information. But, MySQL also allows you to grant permissions to accounts, say for example, that we create a MySQL user account, but then only grant that user permission to select or update records to the database. In this case, if someone breaks in, they cannot drop a table for example, or drop an entire database.
Lets head back to the command-line and look at some examples of what this looks like in practice. We will connect to the database again by running, mysql -u root -p, and then entering the password.
So, we are going to be adding a new user to the MySQL server, then granting that user permissions to a specific database. We can get a listing of current users, by running, select user from mysql.user group by user. So, you can see we have one entry here for the root user. You will notice that we used a new notation here, we are asking for records from the mysql database and the user table, I just wanted to highlight this, as we will do something similar in a moment. Then you will notice, that I am using the group by option here, that is because a single user can have multiple entries, since we have a line for each place it will connect from, for example, localhost, or some external address.
mysql> select user from mysql.user group by user; +------+ | user | +------+ | root | +------+ 1 row in set (0.00 sec)
So, lets create an example user call wiki. I am just going to copy and paste this, as it be a little long. So, we are creating a user called wiki at localhost, and assigning this account a password over here. Next, lets grant permissions to this user, and you will often see tutorials online about granting access to a user, doing something like this. The first mistake is that we are granting all privileges to the user account. Does the user really need these permissions? For example, in episode #21, we looked at using a SQL injection attack to abuse MySQL permissions to read and write files to the OS, specifically the system password file. For this reason, I think granting all permissions is a mistake. The second mistake, is that we are granting, all permissions, to all databases and all tables, this is what this . means. Just like up here where we pulled records out of the mysql database and the user table, we are essentially granting this wiki user access to everything, and that is a bad idea.
mysql> CREATE USER 'wiki'@'localhost' IDENTIFIED BY '$$72!2534Ef3'; Query OK, 0 rows affected (0.00 sec)
GRANT ALL PRIVILEGES ON *.* TO 'wiki'@'localhost';
So, here is what I suggest. Using something like this, granting specific permissions, things like select, insert, update, and then limiting these permissions to a specific database, for example the wiki database. This is much more sane and will prevent an entire range of issues from biting you. Now, lets rerun our command to find users in the database, and as you can see we have the wiki user now. Finally, lets flush these changes to make them active. Alright, that is user access in a nutshell.
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES ON `wiki`.* TO 'wiki'@'localhost'; Query OK, 0 rows affected (0.00 sec)
mysql> select user from mysql.user group by user; +------+ | user | +------+ | root | | wiki | +------+ 2 rows in set (0.00 sec)
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
To finish this episode off, lets review how to quickly backup and restore you databases. You can use the mysqldump command for this. Typical use case, would be to create one off backup before an upgrade, or have cron triggering a mysqldump on a recurring basis, typically daily. This allows you to go back to a point in time if needed. So, lets look at how this would work.
First, lets look at the mysqldump man page, you can find pretty much anything in here, for example, lets scroll all the way to the bottom of the manual page and look at the examples section. So, here you have an example, of backing up a database using mysqldump, the database name, and then piping the output to a backup file. Then to restore the database, you use the mysql command we looked at earlier, and pipe the backup file into it. There are all types of options though, so you will need to find what fits your needs.
$ man mysqldump
So behind the scenes, I have recreated the episode38 database we deleted earlier, and we will use that as an example database here. Lets try and backup our episode38 database by running, mysqldump -u root -p, then we specify the database, in this instance episode38, finally, lets pipe the output to a file, lets call it episode38 and then add a dynamic date to the end. Next, we are prompted for a password, this poses a problem for automation, but we will fix this in a minute. Okay, looks like the file was created, lets have a quick look. Cool, so we can see out blog table here, then down here we can see our table records. So, lets attempt a restore. Lets connect to the MySQL server and drop the episode38 database, and it is now deleted. We have a bit of a problem though, in that the database dump file does not contain the needed create database lines, these are typically not part of the dump. I am not really sure why not, you get them if you dump all databases, lets just have a closer look. So, you can see in this block, there is nothing saying to create the episode38 database, and to use it for the restore. We can attempt a restore and see what the error looks like. We can use the mysql command from earlier in this episode, then pipe in our database dump file, like so. Here you can see the error message, saying no database selected. I wanted to highlight this issue, as you will likely run into something similar.
$ mysqldump -u root -p episode38 > episode38-`date +%F`.sql Enter password:
$ ls -l -rw-r--r-- 1 root root 1904 Oct 7 05:03 episode38-2014-10-07.sql
$ cat episode38-*.sql
$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 5.1.73-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> drop database episode38; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ 2 rows in set (0.00 sec) mysql> quit Bye
$ head episode38-2014-10-07.sql -- MySQL dump 10.13 Distrib 5.1.73, for redhat-linux-gnu (x86_64) -- -- Host: localhost Database: episode38 -- ------------------------------------------------------ -- Server version 5.1.73-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */;
$ mysql -u root -p < episode38-2014-10-07.sql Enter password: ERROR 1046 (3D000) at line 22: No database selected
# Updated: Oct 28, 2014 -- a reader was nice enough to mail in with a solution for the missing create + use episode38 statements mysqldump --databases -u root -p episode38 # For multiple databases mysqldump --databases -u root -p episode38 episode39 ...
The fix is to edit the dump file like so, and then add these two lines, which you should probably understand by now. We are going to create a database called episode38, and then use that database, and that is it, problem solved.
$ vi episode38-2014-10-07.sql create database episode38; use episode38;
Now we can rerun our restore command, which pipes in the database dump, and you will notice that we did not get an error this time. We can verify it worked by connecting to MySQL, showing the databases, showing the tables for the episode38 database, and finally selecting rows from the table. Backups and restores are pretty easy with MySQL, the major limiting factor is the database size, but if you are running a simple Wordpress, Drupal, or Rails app, you will likely have no issues. There are other backup options too, for example XtraBackup, which offers advanced backup features, like non blocking backups, this can be useful if you have larger database sizes. Link in the episode notes below.
$ mysql -u root -p < episode38-2014-10-07.sql Enter password:
$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 5.1.73-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | episode38 | | mysql | +--------------------+ 3 rows in set (0.00 sec) mysql> use episode38; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +---------------------+ | Tables_in_episode38 | +---------------------+ | blog | +---------------------+ 1 row in set (0.00 sec) mysql> select * from blog; +------+------------------------+ | id | content | +------+------------------------+ | 1 | first blog posting | | 2 | second example posting | | 3 | final posting | +------+------------------------+ 3 rows in set (0.00 sec) mysql> quit Bye
Finally, to finish off this episode, lets save our credentials, so that we are not prompted each time we want to do a database backup, which is a must for automated backups with cron. You can do this by creating a my.cnf file in the users home directory, this would be the user who executing the mysqldump command. The format is actually pretty easy, type mysqldump on the first line, user equals the user account, in this case root, but you should be creating role accounts, and finally the password equals the password on the last line. You should also change the ownership of the file to 600 so that only you have access.
$ mysqldump -u root -p episode38 > episode38-`date +%F`.sql Enter password:
$ cat > ~/.my.cnf [mysqldump] user=root password=$$72!2534Ef3
$ chmod 600 ~/.my.cnf
Now instead of using the old syntax and being prompted for the password like this. You simply use the same command, but remove the user and password options. The mysqldump command knows to check for the configuration file and pulls your credentials from it. We can verify this again by deleting the backup and rerunning the backup script.
$ mysqldump episode38 > episode38-`date +%F`.sql
$ rm episode38-2014-10-07.sql rm: remove regular file `episode38-2014-10-07.sql'? y
$ mysqldump episode38 > episode38-`date +%F`.sql
$ ls -la -rw-r--r-- 1 root root 1904 Oct 7 05:15 episode38-2014-10-07.sql
Well, that basically wraps up this episode. Hopefully you were able to learn something new here. If you want to continue on learning about MySQL, I highly suggest checking out the MySQL Performance Blog, you can find a link to it in the episode notes below. The High Performance MySQL book is also really good, and you can find it on Safari Books Online too. I should also mention that we did most of this on the command line to show you how things work under the hood, but there is a really cool web interface for MySQL, called phpmyadmin, and you can do everything we did here today and more, link is in the episode notes below.