A beginners guide to setting up MySQL – Ubuntu

Posted by | Posted in Code, Command-Line, Guides, Linux, MySQL, Ubuntu | Posted on 20-07-2009

MySQL is a very popular and widely used database system that can be used on almost any operating system, but primarly MySQL is used on LAMP (Linux/Apache/MySQL/PHP) systems.

Setting up a MySQL server is fairly easy. If you already know how to work a commmand-line interface, then you are well on your way to setting up a MySQL system. There are (4) things that you need to do, in-order to use a MySQL database system.

1) Install MySQL Server
2) Set a password for the root account
3) Set limitation privileges on the root account
4) Create a database

Installing mysql server via apt-get

$ sudo apt-get install mysql-server

On a default install, the root users does not need a password to access any databases and you can login with no password like this.

$ mysql -u root

If a password is required, use the extra switch -p:

$ mysql -u root -p
Enter password:

To add a root password when there isn’t one. Use the following command. Be sure to change the PASSWORD to your own password and to make sure that it is encapsulated with quotation marks.

mysqladmin -u root password "PASSWORD"

Now that we have the root account locked down with a password, we need to change its accessibility. While you are logged into mysql used these following commands to secure the root account.

mysql> use mysql;
mysql> delete from user where Host like "%";
mysql> grant all privileges on *.* to root@"%.DOMAIN.COM" identified by 'PASSWORD' with grant option;
mysql> grant all privileges on *.* to root@localhost identified by 'PASSWORD' with grant option;
mysql> flush privileges;
mysql> exit;

Now that we can login and feel secure, we create a database. Change DATABASE to any name you’d like.

mysql> create database DATABASE;
Query OK, 1 row affected (0.00 sec)

And that’s it. You can now check that you can connect to the MySQL server using this command:

$ mysql -u USER -p 'PASSWORD' DATABASE_NAME
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 61
Server version: 5.0.75-0ubuntu10.2 (Ubuntu)
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql>

Or, If you want to check your version

mysqladmin -u root -p version
 
Enter password: 
mysqladmin  Ver 8.41 Distrib 5.0.75, for debian-linux-gnu on i486
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
 
Server version          5.0.75-0ubuntu10.2
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 3 hours 7 min 4 sec
 
Threads: 2  Questions: 6596  Slow queries: 0  Opens: 969  Flush tables: 1  Open tables: 19  Queries per second avg: 0.588

If you need to start|stop|restart MySQL, use the commands in /etc/init.d/mysql

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start
sudo /etc/init.d/mysql restart

Notice :

preforming the above security options for the root account will break the /etc/init.d/mysql scripts.

sudo ./mysql status
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'
 * 
@:/etc/init.d$ sudo /etc/init.dmysql restart
 * Stopping MySQL database server mysqld
   ...fail!
 * Starting MySQL database server mysqld
   ...done.
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'

To fix this issue, you need to edit the /etc/mysql/debian.cnf file. There are (2) lines that need to be changed. Change the password hash to a new password. Unfortunately, this password is plain text.

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = XXXXXXXXXXXXX
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
user     = debian-sys-maint
password = XXXXXXXXXXXXXX
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

Then you need to log into MySQL and permissions to the “debian-sys-maint” user account.

> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'NEWPASSWORD' WITH GRANT OPTION
 
> FLUSH PRIVILEGES;

Now, if for some reason, you overwrite, or toast your root password, You need to reset it. At this point in time, the start|stop features of /etc/init.d/mysql will probably no longer work and you need to kill the process old school style.

ps -aux |grep mysql
 
root      5340  0.0  0.0   1872   548 pts/2    S    05:17   0:00 /bin/sh /usr/bin/mysqld_safe
mysql     5379  0.0  3.5 129412 27116 pts/2    Sl   05:17   0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
root      5381  0.0  0.0   3052   704 pts/2    S    05:17   0:00 logger -p daemon.err -t mysqld_safe -i -t mysqld
root      5543  0.0  0.1   3336   792 pts/2    R+   05:23   0:00 grep mysql

What you are seeing here is a list of processes that contain the word “mysql”. From left to right you can see there is a USER and the PROCESS ID (4 digits), LOAD variables, RUN time and then the COMMAND. What we are interested in is the (4) digit process ID numbers. In this case, 5340, 5379 and 5381. The 5543 process, as you can see is “grep mysql”. This is our search process ID and most likely doesn’t exist anymore.

In-order to stop those (3) processes, you need to kill them. Literally. You need to use the “kill” command to stop the process from running, and to keep it from relaunching, you add the “-9″. This refers to the type of kill you are preforming. In this case the kill cannot be blocked and won’t relaunch.

# sudo kill -9 5340
# sudo kill -9 5379
# sudo kill -9 5381
kill: No such process

As you can see the 5381 process no longer existed after we killed the 5379 process.

Now that we have mysql completely stopped, we can restart the mysql server and change the root password. To be able to do this start mysql with the “–skip-grant-tables” followed by the “&” character. The skip grant tables will bypass the mysql database and won’t load the current usernames and passwords. the “&” will embed the process in the background, so you can keep working from the same command-shell.

sudo mysqld --skip-grant-tables &
 
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
090720  5:42:50  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
090720  5:42:50  InnoDB: Started; log sequence number 0 115369069
090720  5:42:50 [Note] mysqld: ready for connections.
Version: '5.0.75-0ubuntu10.2'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

Now that MySQL is restarted, go ahead and log back into the server and use the mysql database. Notice, we don’t need a password this time.

mysql -u root
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.75-0ubuntu10.2 (Ubuntu)
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
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

Now lets change the root password.

 
mysql> UPDATE user SET password = password('NEW PASSWORD') WHERE user = 'root' AND host='localhost';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

The Rows mached:1, means (1) row was updated and the password was updated successfully!

Flush your privileges and quit.

mysql> FLUSH privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> quit;
Bye

Now restart your mysql server to reactivate the accounts and passwords.

sudo /etc/init.d/mysql restart
 * Stopping MySQL database server mysqld
090720  5:52:22 [Note] mysqld: Normal shutdown
090720  5:52:22  InnoDB: Starting shutdown...
090720  5:52:23  InnoDB: Shutdown completed; log sequence number 0 115369069
090720  5:52:23 [Note] mysqld: Shutdown complete
   ...done.
 * Starting MySQL database server mysqld
   ...done.
 * Checking for corrupt, not cleanly closed and upgrade needing tables.
[1]+  Done                    sudo mysqld --skip-grant-tables

Comments are closed.

Join the mailing list

Check your email and confirm the subscription