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: 0The 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