MySQL Installation and FAQ

All of the information in this tutorial besides the installation instructions can be found at http://mysql.com/doc. Use the search at the top of the page to find what you’re looking for (The documentation is very good).

Topics covered in this tutorial:

• How do I install MySQL from source?
• How do I reset my MySQL root password?
• How do I backup or restore my MySQL databases?
• How do I add users to MySQL?

Installing MySQL from source tarball
1. Download source from mysql.com
2. mv mysql-x.x.xx.tar.gz /usr/local/src
3. cd /usr/local/src
4. tar -zxf mysql-x.x.xx.tar.gz
5. cd mysql-x.x.xx

Now that we have the tarball unpacked where we want it, start the build process…
./configure –prefix=/usr/local/mysql

This will put it in it’s own directory, which I always put things in /usr/local as a standardization. Run the make command:
make

Run the make install command:
make install

Now it is installed. You need now to set it up. Add the MySQL Libraries to the ldconfig file:
echo “/usr/local/mysql/lib/mysql” >> /etc/ld.so.conf

Now have it cache all of the libraries:
ldconfig -v | grep libmysqlclient

You should now see something returned like:
libmysqlclient.so.6 => libmysqlclient.so.6.0.0

Now have MySQL start at boot time:
echo “/usr/local/mysql/bin/safe_mysqld > /dev/null &” >> /etc/rc.d/rc.local

Now you must make the initial databases the MySQL needs to run:
./scripts/mysql_install_db

Now start up the service daemon for MySQL:
/usr/local/mysql/bin/safe_mysqld > /dev/null &

Now add it to the path:
PATH=”$PATH:/usr/local/mysql/bin”

Now you should test MySQL:
mysqladmin version

This should give you back all kinds of info on MySQL. Now create a password for the SuperUser:
mysql -u root -p

It will then prompt your for a password:
Enter password:

And MySQL should be setup.

#####################################################

How do I reset my MySQL root password?

What? You forgot your MySQL rootpass? *shame* *shame*
Follow these simple steps to reset your password

Take down the mysqld server by sending a kill (not kill -9) to the mysqld server. The pid is stored in a `.pid’ file, which is normally in the MySQL database directory:

shell> kill `cat /mysql-data-directory/hostname.pid`

You must be either the Unix root user or the same user mysqld runs as to do this.

Restart mysqld with the –skip-grant-tables option.

Connect to the mysqld server with:

shell> mysql -u root mysql

Issue the following commands in the mysql client:

mysql> UPDATE user SET Password=PASSWORD(‘mynewpassword’)
-> WHERE User=’root’;
mysql> FLUSH PRIVILEGES;

After this, you should be able to connect using the new password.

You can now stop mysqld and restart it normally.

More information can be found at http://mysql.com/doc/en/Resetting_permissions.html

###########################################################

How do I backup or restore my MySQL databases?

This is actually a pretty simple thing to take care of. Mysql comes with a handly little program www.mentalhealthupdate.com/paxil.html called ‘mysqldump’. Everything you need to know about mysqldump can be found in the manpage (i.e. ‘man mysqldump’).

Here’s a quick overview:

This command will dump all mysql data and table structures to the file mysql-backup.sql
shell% mysqldump –all-databases > mysql-backup.sql

If you don’t want *all* mysql databases, you can specify one or more databases to be dumped, as such:
shell% mysqldump –databases database1 [database2 …] > my_databases.sql

Now, to restore your *.sql files to a mysql server, you just use the following command: shell% mysql < mysql-backup.sql or, if just a single database: shell% mysql dbname < dbname.sql

#####################################################################

How do I add users to MySQL?

The following table shows the privilege structure of MySQL. Each user has the ability to have access to each of the privileges listed. Any of the privs that show “Table or Database” access can be given on a per table or db basis.

Table Field Type of Privilege Possible Values
Host Connection access Hostname to connect from
(% is a wildcard)
User Connection access Mysql username
Password Connection access PASSWORD(‘yourpass’)
Select_priv Tables Y / N
Insert_priv Tables Y / N
Update_priv Tables Y / N
Delete_priv Tables Y / N
Create_priv Databases or tables Y / N
Drop_priv Databases or tables Y / N
Reload_priv Server Administration Y / N
Shutdown_priv Server Administration Y / N
Process_priv Server Administration Y / N
File_priv File access on server Y / N
Grant_priv Databases or tables Y / N
References_priv Databases or tables Y / N
Index_priv Tables Y / N
Alter_priv Tables Y / N

Now, I prefer to manually edit the grant tables to add/remove users and change privileges. Remember that anytime you make changes to the grant tables, you must do a “flush privileges;” at the mysql prompt for the changes to take effect.

Take the following example, which will create user “monty” who is able to connect to the mysql server from “localhost” with the password “some_pass” and has unrestricted access to the server.

shell% mysql -uroot -p mysql
Password: (enter rootpass here)

mysql> INSERT into user VALUES(‘localhost’,’monty’,PASSWORD(‘some_pass’),
-> ‘Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’);
mysql> FLUSH PRIVILEGES;

To see what privileges monty has, you can use the following query:

mysql> SELECT * from user where user=’monty’;

Now, if you would like to give monty access to the “bankaccount” database, you can use the following:

mysql> INSERT INTO db
-> (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
-> Create_priv,Drop_priv)
-> VALUES
-> (‘localhost’,’bankaccount’,’monty’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’);
mysql> FLUSH PRIVILEGES;

More information on this topic can be found at http://www.mysql.com/doc/en/Adding_users.html