MySQL: Installation and Configuration
Introduction
MySQL is a relational SQL database server developed with a focus on high performance. It is multi-threaded, robust, and multi-user. It is open-source software developed under a dual license depending on its use: in an open-source product or in a proprietary product. In the latter case, the license is paid; otherwise, it’s free.
Installation
To install it, nothing could be simpler:
|
|
Usage
I strongly recommend a small built-in MySQL utility to configure MySQL simply and securely. Let’s start:
|
|
Just press “Enter” because there is no default password
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
You already have a root password set, so you can safely answer 'n'.
Change the root password? [Y/n]
Answer “y” and change the password
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]
Answer “y” to remove anonymous users
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]
Answer “y” to not allow root to connect remotely
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]
Answer “y” to remove the test database
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n]
And finally answer “y” to reload the privilege tables
Cleaning up...
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!
Everything is finished; you can now use your database.
Creating a database
To create a database, here is the command to execute:
|
|
Creating a user
To create a user:
|
|
Changing a user’s password
To change a user’s password, it’s simple:
|
|
This command includes flush privileges, so you don’t need to type it afterward :-)
Modifying user rights
If, for example, I want to change the connection hostname for all users:
|
|
Deleting a user
Before deleting a user, it is advisable to list the privileges to revoke current rights:
|
|
Then revoke:
|
|
And finally, delete the user:
|
|
Listing running processes
|
|
Renaming a database
|
|
Run this script and it will rename table by table to finally create the new database. This is the method recommended by MySQL.
Knowing the size of a database
To get the size of all databases in MB:
|
|
If you want the size of a single table, you need to specify the table name (table_name field) and the database name (table_schema field):
|
|
Connecting with default credentials
It can be useful to be able to connect simply without having to enter credentials. Here is a very simple method that consists of entering your credentials in a file in your home:
|
|
Then apply the right permissions:
|
|
Connect without credentials :-)
FAQ
How to reset your root password when you’ve lost it?
Have you ever forgotten the root password on one of your MySQL servers? No? Well maybe I’m not as perfect as you. This is a quick h00tow (how to) reset your MySQL root password. It does require root access on your server. If you have forgotten that password wait for another article:
First things first. Log in as root and stop the mysql daemon. Now let’s start up the mysql daemon and skip the grant tables which store the passwords.
|
|
You should see mysqld start up successfully. If not, well you have bigger issues. Now you should be able to connect to mysql without a password.
|
|
Now kill your running mysqld, then restart it normally. You should be good to go. Try not to forget your password again.
Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist
If you get this kind of message when booting mysql:
Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
You need to rebuild the missing databases like this:
|
|
Resources
Last updated 09 Apr 2014, 16:39 CEST.