14 DAY TRIAL // MySQL (My Structured Query Language) is a multi-threaded, multi-user, SQL Database Management System with more than 6 million installations all over the world which makes it the most popular language for adding, accessing and processing data in a database. One of the reasons MySQL is so widely used might be the fact that is an open source software, allowing anyone and everyone to download and use it, of course, in accordance with the GPL license. It's been noted mainly for its speed, flexibility and reliability. However, many users agree that MySQL works best when managing content and not executing transactions.
MySQL is popular for web applications such as forums, content management system, web blogs and so on. Most of those web applications are written in PHP, which is closely tied to MySQL.
To administer MySQL databases, you can use the included command-line tool (mysql and mysqladmin) or a GUI administration tool such as MySQL Administrator or MySQL Query Browser. Also, you can use the widespread web based administration tool, phpMyAdmin, which is written in PHP.
Installing
In most cases, MySQL might already be installed. If this is not the case, you will probably want to install quick and easy, using RPMs. There are a number of dependencies that may be needed so it's highly recommended to use your distribution's package manager (such as Yum). The packages you want to search and install are mysql and mysql-server.
Starting
Before anything else, you might want to configure MySQL to start automatically at Linux boot time. For this, use the chkconfig tool:
# chkconfig mysqld on
You can start, stop and restart MySQL using the commands:
# service mysqld start # service mysqld stop # service mysqld restart
Creating a MySQL 'root' Account
MySQL stores all its users and passwords in a special database called 'mysql'. Users can be added to this database including the databases to which they have access to. To set a root password, you only have to execute a simple command for a fresh installation:
# mysqladmin -u root password enter-your-password-here
This is not the case for a forgotten root password. If you want to CHANGE the root password, you will have to do a root password recovery first.
Accessing the command line
To access the MySQL command line, you have to execute the command and enter the new set root password when prompted:
# mysql -u root -p
Remember that all MySQL CLI commands end with a semi-colon (;) including the exit command.
Creating and Deleting MySQL databases
Many Linux applications require a database to be created before the software is installed. The procedure is rather simple, just enter the MySQL CLI and type the command:
mysql> create database somesoftdb;
The delete procedure is just a simple, from the MySQL CLI type:
mysql> drop database somesoftdb;
Granting Privileges
Some applications will not only require a database of their own, but also a username. It's not a good idea to use the root user account, because of its universal privileges, so this is how a user can be added. Drop into a MySQL CLI and type:
mysql> grant all privileges on somesoftdb.* to somesoftuser@"localhost" identified by 'somepasswd';
Next, you will have to write the privilege changes to the mysql.sql:
mysql> flush privileges;
Loading .sql Scripts
Sometimes, you will bump into applications that require you to create a database and also the databases's tables. Fortunately, most if not all of these applications provide a sql script that does all the work for you. However, be careful: before you load an sql script, you have to create its database or else you will only get a nasty error. This time, the script is loaded using the Linux bash command line:
# mysql -u root -p somesoftdb < some_soft.sql
Other MySQL CLI Commands
In order to view all the databases created on your server, login to MySQL as root and type the following command. But if you are only interested in a certain user's databases, you will have to login with that username.
mysql> show databases;
To list the tables a database has:
mysql> use somesoftdb; show tables;
To view the data contained in a certain table (I'll use soft_table for example):
mysql> select * from soft_table;
Recovering a Forgotten MySQL root Password
First, you have to stop the MySQL server:
# service mysqld stop
In order to modify the root password, the MySQL server has to be started in safe mode:
# mysqld_safe --skip-grant-tables --skip-networking &
For the moment, MySQL is running without password protection, so this is the easy part. Login to the MySQL server using the command:
# mysql -u root
And:
mysql> use mysql; mysql> UPDATE user SET Password=PASSWORD("your-new-password-here") WHERE User="root";
Exit the MySQL CLI and restart the daemon:
mysql> exit; # service mysqld restart
Database backup and restoration
You can back up a database to a single .sql file with the command (from the Linux command line):
# mysqldump --add-drop-table -u somesoftuser -pPasswdForUserHere somesoftdb > somesoftdb-backup.sql
Notice there isn't a space after -p. Also, it's always a good idea to backup the mysql database as it contains all the database user access information.
Restoring a database from a .sql script is done with the command:
# mysql -u somesoftuser -pPasswdForUserHere somesoftdb < somesoftdb-backup.sql
If this is your first time using MySQL, it's recommended that you install smaller MySQL-based applications before starting a bigger project. It's always good to have a little bit of practice before installing an important and official application.