mysql

These notes are distilled from a more detailed guide on mysql from Linux Home Networking.
The /etc/my.cnf file is the main mysql config file.

mysql databases are usually located in the subdir of /var/lib/mysql/.

mysql stores usernames / passwords in database mysql. Add users to this database and specify the databases to which they will have access with the grant command.

The mysql root account used to create and delte databases is the exception. Use the mysqladmin command to set root password.

With mysqld started use the mysqladmin command to set the mysql root password.

# mysqladmin -u root password new-password

If you want to change root password later you can do a root password recovery.

To access the mysql command line, type:

# mysql -u root -p
Enter password:

After you are logged in you can type \h for help, \c to clear the buffer, or \q to quit.

Enter mysql commands with a semi colon — you know the “;” (without quotes).

To create a database:

mysql> create database example;

Sometimes you may have to physically delete a database underneath /var/lib/mysql. Make sure you stop the server first before you do this.

To grant all privileges to a user:

mysql> grant all privileges on database.* to username@"servername" identified by 'password';

The next step is to write the privilege changes to the mysql.sql database using the flush privileges command.

mysql> flush privileges;

To imported ready-made scripts that create database and tables, use this:

# mysql -u root -p example < create_mysql.script

To view your databases in mysql:

# mysql -u mysqluser-p example
mysql> show databases;

To list the tables in your database:

mysql> use databaseexample;
mysql> show tables;

To view the table structure of your database:

mysql> describe example;

RECOVERING, OR CHANGING ROOT PASSWORD

1. Stop mysql
2. Start mysql in safe mode, like so:

# safe_mysqld --skip-grant-tables

3. Reset the password:

# mysqladmin -u root flush-privileges password "newpassword";

4. Restart mysql normally.

mysql database backup and recovery

# mysqldump --add-drop-table -u mysqluser -ppasswd database > /tmp/database.sql

Note: make sure there are no spaces between the -p switch and the password. Always backup the database named “mysql” too, because it contains user access information.

The syntax for restoring a mysql database:

# mysql -u username -ppassword database < backedup-database

MYSQL SECURITY

# netstat -an

Your server will be listening on ip address 0.0.0.0 (all) on TCP port 3306.

The problem w/ this is that it exposes your database to mysql queries from the internet. If your sql database is going to be accessed only by applications running on the server itself, then you can force it to listen only to the equivalent of the loopback.

To do this: edit the /etc/my.conf file and use the bind-address directive in the [mysqld] section to define the specific ip address on which mysqld listens for connections.

[mysqld]
bind-address=127.0.0.1

Restart mysql.

The nestat -an command will show mysql listening on only the loopback address on TCP port 3306.

mysql errors are logged automatically in the /var/log/mysqld.log.

---

Starting, stopping, restarting, anc verifying mysqld daemon.

# chkconfig mysqld on
# service mysqld start
# service mysqld stop
# service mysqld restart
# pgrep mysqld