MySQL Quick Reference

Lots of this was taken from here: http://www.pantz.org/software/mysql/mysqlcommands.html

Figured since I referenced it so much …

Show replication slave status.

mysql> show slave status;

Show process list.

mysql> show processlist; (show full proccesslist;)

Show variables.

mysql> show variables like ‘%collation%’;

To login (from unix shell) use -h only if needed.

# [mysql dir]/bin/mysql -h hostname -u root -p

Create a database on the sql server.

mysql> create database [databasename];

Create a database on the sql server setting to UTF8

mysql> CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

List all databases on the sql server.

mysql> show databases;

Switch to a database.

mysql> use [db name];

To see all the tables in the db.

mysql> show tables;

To see database’s field formats.

mysql> describe [table name];

To delete a db.

mysql> drop database [database name];

To delete a table.

mysql> drop table [table name];

Show all data in a table.

mysql> SELECT * FROM [table name];

Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

Show certain selected rows with the value “whatever”.

mysql> SELECT * FROM [table name] WHERE [field name] = “whatever”;

Show all records containing the name “Bob” AND the phone number ‘3444444’.

mysql> SELECT * FROM [table name] WHERE name = “Bob” AND phone_number = ‘3444444’;

Show all records not containing the name “Bob” AND the phone number ‘3444444’ order by the phone_number field.

mysql> SELECT * FROM [table name] WHERE name != “Bob” AND phone_number = ‘3444444’ order by phone_number;

Show all records starting with the letters ‘bob’ AND the phone number ‘3444444’.

mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ‘3444444’;

Show all records starting with the letters ‘bob’ AND the phone number ‘3444444’ limit to records 1 through 5.

mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ‘3444444’ limit 1,5;

Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a.

mysql> SELECT * FROM [table name] WHERE rec RLIKE “^a”;

Show unique records.

mysql> SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in an ascending (asc) or descending (desc).

mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

Return number of rows.

mysql> SELECT COUNT(*) FROM [table name];

Sum column.

mysql> SELECT SUM(*) FROM [table name];

Join tables on common columns.

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES(‘%’,’username’,PASSWORD(‘password’));
mysql> flush privileges;

Change a users password from unix shell.

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password ‘new-password’

Change a users password from MySQL prompt. Login as root. Set the password. Update privs.

# mysql -u root -p
mysql> SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);
mysql> flush privileges;

Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

# /etc/init.d/mysql stop
# mysqld_safe –skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

Set a root password if there is on root password.

# mysqladmin -u root password newpassword

Update a root password.

# mysqladmin -u root -p oldpassword newpassword

Allow the user “bob” to connect to the server from localhost using the password “passwd”. Login as root. Switch to the MySQL db. Give privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by ‘passwd’;
mysql> flush privileges;

Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,’databasename’,’username’,’Y’,’Y’,’Y’,’Y’,’Y’,’N’);
mysql> flush privileges;

or

mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

To update info already in a table.

mysql> UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where [field name] = ‘user’;

Delete a row(s) from a table.

mysql> DELETE from [table name] where [field name] = ‘whatever’;

Update database permissions/privilages.

mysql> flush privileges;

Delete a column.

mysql> alter table [table name] drop column [column name];

Add a new column to db.

mysql> alter table [table name] add column [new column name] varchar (20);

Change column name.

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

Make a unique column so you get no dupes.

mysql> alter table [table name] add unique ([column name]);

Make a column bigger.

mysql> alter table [table name] modify [column name] VARCHAR(3);

Delete unique from table.

mysql> alter table [table name] drop index [colmn name];

Load a CSV file into a table.

mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);

Dump all databases for backup. Backup file is sql commands to recreate all db’s.

# [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql

Dump one database for backup.

# [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql

Dump a table from a database.

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

Get stored procedures for a database (if you have the permissions)

mysqldump -u root -p  –routines –no-create-info –no-data –no-create-db –skip-opt database_name > file_name.sql

MySQL accepts network connections

Make sure skip-networking is commented out in my.cnf file.

Test with: netstat -antulp | grep LISTEN

tcp        0      0 127.0.0.1:199               0.0.0.0:*                   LISTEN      2297/snmpd          
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      4485/mysqld         
tcp        0      0 :::22                       :::*                        LISTEN      2334/sshd

The mysqld entry will not be listed if MySQL is not accepting network connections.

MySQL Replication

The steps I followed to get a MySQL server and one MySQL slave setup were taken from here: http://onlamp.com/pub/a/onlamp/2005/06/16/MySQLian.html

Live backups of MySQL using Replication

Here’s how to setup a replication slave to ensure reliable backups without having to shutdown the server. Typically, replication is a system configuration whereby the MySQL server, known in this context as a master server, houses the data and handles client requests, while another MySQL server (a slave server) contains a complete copy of the data and duplicates all SQL statements in which data is changed on the master server right after it happens. There are several uses for replication (e.g. load balancing), but the concern of this article has to do with using replication for data backups.  You can set up a separate server to be a slae and then once a day turn replication off to make a clean backup.  When you’re done, replication can be restarted and the slave will automatically query the master for the changes to the data that it missed while it was offline. Replication is an excellent feature and it’s part of MySQL.

The Replication Process

Before explaining how to setup replication, let’s quickly explain the steps that MySQL goes through to maintain a replicated server. The process is different depending on the version of MySQL. For purposes of this post, the process will be for version 4.0 or higher, since most systems now are using later versions.

When replication is running, basically, as SQL statements are executed on the master server, MySQL records them in a binary log (bin.log) along with a log position identification number.  The slave server in turn, through an IO thread, regularly and very often reads the mater’s binary log for any changes. If it finds a change, it copies the new statements to its relay log (relay.log). It then records the new position identification number in a file (master.info) on the slave server. The slave then goes back to checking the master binary log, using the same IO thread. When the slave server detects a change to its relay log, through an SQL thread the slave executes the new SQL statement recorded in the relay log. As a safeguard, the slave also queries the master server through the SQL thread to compare its data with the master’s data. If the comparison shows inconsistency, the replication process is stopped and an error message is recorded in the slave’s error log (error.log). If the results of the query match, the new log position identification number is recorded in a file on the slave (relay-log.info) and the slave waits for another change to the relay log file.

This process isn’t a significant drain on the master server and occurs quickly.  Also, it’s surprisingly easy to set up. It only requires a few lines of options to be added to the configuration file (my.cnf) on the master and slave servers.  If you’re dealing with a new server, you’ll need to copy the databases on the master server to the slave to get it caught up. Then it’s merely a matter of starting the slave for it to begin replication.

The Replication User

There are only a few steps to setting up replication.  The first set is to setup a user account to use only for replication. It’s best not to use an existing account for security reasons. To do this, enter a SQL statement like the following on the master server, logged in as root or a user that has GRANT OPTION privileges:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘replicant_user’@’slave_host’ IDENTIFIED BY ‘strong_password’;

flush privileges;

In this SQL statement, the user account replicant_user is granted only what’s needed for replication. The user name can be almost anything. The host name (or IP address) is given in quotes. You should enter this same statement on the slave server with the same user name and password, but the master’s hose name or IP address. This way, if the master fails and will be down for a while, you could redirect users to the slave with DNS or by some other method. When the master is back up, you can then use replication to get it up to date by temporarily making it a slave to the former slave server. Incidentally, if you upgraded MySQL to version 4.0 recently, but didn’t upgrade your mysql database, the GRANT statement above won’t work because these privileges didn’t exist in the earlier versions.

Configuring the Servers

Once the replication user is set up on both servers, we will need to add some lines to the MySQL configuration file on the master and on the slave server.  Depending on the type of OS, the file will probably be called my.cnf or my.ini. Using a text editor, add the following lines to the configuration file, under the [mysqld] section.

server-id = 1

log-bin = /var/log/mysql/bin.log

The server ID is an arbitrary number to identify the master server.  Almost any whole number is fine. A different one should be assigned to the slave server to keep them straight.  The second line instructs MySQL to perform binary logging to the path and file given. Be sure the directory and the file are owned by the mysql user and at least has permission to write to the directory. Also, for the file name use the suffix of “.log” as show here. It will be replaced automatically with an index number (e.g. “.000001”) as new log files are created when the server is restarted or the logs are flushed.

For the slave server, we will need to add a few more lines to the configuration file.  We’ll have to provide information on connecting to the master server, as well as more log file options. We would add the lines similar to the following to the slave’s configuration file:

server-id = 2

master-host = mastersite.com
master-port = 3306
master-user = replicant
master-password = my_pwd

log-bin = /var/log/mysql/bin.log
log-bin-index = /var/log/mysql/log-bin.index
log-error = /var/log/mysql/error.log

relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index

This may seem like a lot, but it’s pretty straightforward.  The first line is the identification number for the slave server. If you set up more than one slave server, give them each a different number.  The next set of lines provides information on the master server: the host name as shown here, or the IP address of the master may be given. Next, the port to use is given. The next two lines provide the user name and password for logging into the master slave.

The last two stanzas above set up logging. The second to last stanza starts binary logging as we did on the master server, but this time on the slave.  This is the log that can be used to allow the master and the slave to reverse roles, as mention earlier. The binary log index file (log-bin.index) is for recording the name of the current binary log file to use.  As the server is restarted or logs are flushed, the current log file changes and its name is recorded here. The log-error option establishes an error log. If you don’t already have this setup, you should, since it’s where any problems with replication will be recorded. The last stanza establishes the relay log and related files mentioned earlier. The relay log makes a copy of each entry in the master server’s binary log for performance sake, the relay-log-info-file option names the files where the slave’s position in the master’s binary log will be noted, and the relay log index file is for keeping track of the name of the current relay log file to use for replication

Copying Databases and Starting Replication

If you’re setting up a new master server that doesn’t contain data, then there’s nothing left to do but restart the slave server.  However, if you’re setting up replication with an existing server that already has data on it, you will need to make an initial backup of the databases and copy it to the slave server.  There are many methods to do this; for our examples, we’ll use the utility mysqldump to make a backup while the server is running.  However, there’s still the problem with attaining consistency of data on an active server. Considering the fact that once you set up replication you may never have to shut down your server for backups again, it might be worth while to at least lock the users out this one last time to get a clean, consistent backup. To run the master server so that only root has access, we can reset the variable max_connections like so:

SHOW VARIABLES LIKE ‘max_connections’;

SET GLOBAL max_connections = 0;

The first SQL statement isn’t necessary, but we may want to know the initial value of the max_connections variable so that we can change it back when the backup is finished.  Although setting the variable to  a value of 0 suggests that no connections are allowed, one connection is actually reserver for the root user.  Of course, this will only prevent any new connections. To see if there are any connections still running, enter

SHOW PROCESSLIST;

To terminate any active processes, you can use the KILL command. With exclusive access to the server, using mysqldump is usually very quick.  We would enter the following from the command line on the master server:

mysqldump -u root -p –extended-insert –all-databases –master-data > /tmp/backup.sql

This will create a text file containing SQL statements to create all of the databases and tables with data.  The –extended-insert option will create multiple-row INSERT statements and thereby allow the backup to run faster, for the least amount of down time.  The –master-data option above locks all of the tables during the dump  to prevent data from being change, but allows users to continue reading the tables. With exclusive access, this feature isn’t necessary. However, this option also adds a few lines like the following to the end of the dump file:

--
-- Position to start replication from
--

CHANGE MASTER TO MASTER_LOG_FILE='bin.000846' ;
CHANGE MASTER TO MASTER_LOG_POS=427 ;

When the dump file is executed on the slave server, these last lines will record the name of the master’s binarly log file and the position in the log at the time of the backup, while the tables were locked. When replication is started, it will go to this log file and execute any SQL statements recorded starting from the position given. This is meant to ensure that any data changed while setting up the slave server isn’t missed. To execute the dump file to set up the databases and data on the slave server, copy the dump file to the slave, make sure MySQL is running, then enter something like the following:

mysql -u root -p < /tmp/backup.sql

This will execute all of the SQL statements in the dump file, which will include the CREATE and INSERT statements. Once the backed-up databases are loaded onto the slave server, execute the following SQL statement while logged in as root on the slave:

START SLAVE;

After this statement is run, the slave will connect to the master and get the changes it missed since the backup.  From there, it will stay current by continuously checking the binary log as outlined before. You can view the status of the slave by running the following SQL statement while logged in as root on the slave:

SHOW SLAVE STATUS;

If it’s not working check the error log to find out what is not working.

Overcoming MySQL’s 4GB Limit

Information taken from here: http://jeremy.zawodny.com/blog/archives/000796.html

Another useful link: http://dev.mysql.com/doc/refman/5.0/en/full-table.html

When this happens, the first reaction I hear is “You never told me that MySQL has a 4GB limit! What am I going to do?” Amusingly, I usually do describe the limit when I discuss the possibility of using MySQL with various groups–they often forget or underestimate the impact it will have. Putting that aside, the problem is easily fixed, as that page explains. You simply need to run an ALTER TABLE command.

And you’ll need to wait. That ALTER TABLE is going to take some time. Really.

To protect yourself in the future, use the MAX_ROWS and AVG_ROW_LENGTH options at CREATE TABLE time if the table is likely to get big.

InnoDB tables do not have this limitation because their storage model is completely different.

Where does this limit come from?

In a MyISAM table with dynamic (variable length) rows, the index file for the table (tablename.MYI) stores row locations using 32-bit pointers into the data file (tablename.MYD). That means it can address only 4GB of space.

This problem is both a historical artifact and an optimization. Back when MySQL was created, it wasn’t common to store that much data in a single table. Heck, for a long time 4GB was an entire hard disk and most operating systems had trouble with files larger than 2GB. Obviously those days are gone. Modern operating systems have no trouble with large files and hard disks larger than 100GB are quite common.

From an optimization point of view, however, the 32-bit pointer still makes sense. Why? Because most people are running MySQL on 32-bit hardware (Intel/Linux). That will change as use of AMD’s Opteron becomes more widespread, but 32-bit will be the majority for the next few years. Using 32-bit pointers is the most efficient way to do this on 32-bit hardware. And even today, most MySQL installations don’t have tables anywhere near 4GB in size. Sure, there are a lot of larger deployments emerging. They’re all relatively new.

An Example

Here’s a table that you might use to store weather data:

mysql> describe weather;
+-----------+--------------+------+-----+------------+-------+
| Field     | Type         | Null | Key | Default    | Extra |
+-----------+--------------+------+-----+------------+-------+
| city      | varchar(100) |      | MUL |            |       |
| high_temp | tinyint(4)   |      |     | 0          |       |
| low_temp  | tinyint(4)   |      |     | 0          |       |
| the_date  | date         |      |     | 0000-00-00 |       |
+-----------+--------------+------+-----+------------+-------+
4 rows in set (0.01 sec)

To find its size limit, we’ll use SHOW TABLE STATUS

mysql> show table status like 'weather' \G
*************************** 1. row ***************************
           Name: weather
           Type: MyISAM
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 4294967295
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2003-03-03 00:43:43
    Update_time: 2003-03-03 00:43:43
     Check_time: 2003-06-14 15:11:21
 Create_options:
        Comment:
1 row in set (0.00 sec)

There it is. Notice that Max_data_length is 4GB. Let’s fix that.

mysql> alter table weather max_rows = 200000000000 avg_row_length = 50;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show table status like 'weather' \G
*************************** 1. row ***************************
           Name: weather
           Type: MyISAM
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 1099511627775
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2003-06-17 13:12:49
    Update_time: 2003-06-17 13:12:49
     Check_time: NULL
 Create_options: max_rows=4294967295 avg_row_length=50
        Comment:
1 row in set (0.00 sec)

Excellent. Now MySQL will let us store a lot more data in that table.

Too Many Rows?

Now, the astute reader will notice the Create_options specify a limit of 4.2 billion rows. That’s right, there’s still a limit, but now it’s a limit on number of rows, not the size of the table. Even if you have a table with rows that are 10 times as large, you’re still limited to roughly 4.2 billion rows.

Why?

Again, this is 32-bit hardware. If you move to a 64-bit system, the limit is raised accordingly.

MySQL and JOINs

There is a great write up about the differences between JOINs in MySQL here:

http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html

Basically, a JOIN only returns records that match.

A LEFT JOIN will returns all rows that match and IN ADDITION all rows in the left table that do not match as well.

A RIGHT JOIN is similar to left except all rows from the RIGHT table are returned.

INNER JOIN returns all rows in both tables.

What does FLUSH TABLES WITH READ LOCKS do?

The following info. was taken from here:http://mysqlha.blogspot.com/2008/07/what-exactly-does-flush-tables-with.html

FLUSH TABLES WITH READ LOCK can do wonders. But you should understand what it does to avoid problems. The manual describes it with this:

Closes all open tables and locks all tables for all databases with a read lock until you explicitly release the lock by executingUNLOCK TABLES. This is very convenient way to get backups if you have a filesystem such as Veritas that can take snapshots in time.
FLUSH TABLES WITH READ LOCK acquires a global read lock and not table locks, so it is not subject to the same behavior as LOCK TABLES and UNLOCK TABLES with respect to table locking and implicit commits:

The implementation does this:

  1. set the global read lock – after this step, insert/update/delete/replace/alter statements cannot run
  2. close open tables – this step will block until all statements started previously have stopped
  3. set a flag to block commits

If long-running statements were started prior to step 2, then the FLUSH TABLES WITH READ LOCK command will block until they complete. This is a bad state to get stuck in as the server is in read-only mode at this point and this statement is frequently run on a primary. It might be a good idea to kill long-running statements prior to running the FLUSH command or when it gets stuck. Note that commit can still be done until step 3 finishes.
Here is the code:

if (lock_global_read_lock(thd))
return 1;                               // Killed
result=close_cached_tables(thd,(options & REFRESH_FAST) ? 0 : 1,
tables);
if (make_global_read_lock_block_commit(thd)) // Killed
{
/* Don’t leave things in a half-locked state */
unlock_global_read_lock(thd);
return 1;

Test Joomla MySQL Connection

I recently ran into an issue with a Joomla site where users couldn’t login with a connection to database error.  I went poking around Google to find this neat test script.  http://joomla-r-us.com/faq/87-mysql-database-connection-test-script

<?php
///////////////////////////////////////////////////
// Joomla-R-Us
//
// Connection test to external MySQL database
//
///////////////////////////////////////////////////

// Configurations below!!

/////////  Bootstrap the Joomla Framework //////////////

define( ‘_JEXEC’, 1 );

define(‘JPATH_BASE’, dirname(__FILE__) );

define( ‘DS’, DIRECTORY_SEPARATOR );

require_once ( JPATH_BASE .DS.’includes’.DS.’defines.php’ );
require_once ( JPATH_BASE .DS.’includes’.DS.’framework.php’ );

/**
* CREATE THE APPLICATION
*
* NOTE :
*/
$mainframe =& JFactory::getApplication(‘site’);

/**
* INITIALISE THE APPLICATION
*
* NOTE :
*/
// set the language
$mainframe->initialise();

JPluginHelper::importPlugin(‘system’);

// trigger the onAfterInitialise events
$mainframe->triggerEvent(‘onAfterInitialise’);

/////////////////////////////////////////////////////

///////////// Configure this ////////////////////////

$hostname     = ‘myhost.mydomain.com’;
$username     = ‘myaccount’;
$password     = ‘mypassword’;
$database     = ‘joomla’;

///////////// End Configure ////////////////////////

// Try connecting to the database

$option = array ();
$option [‘driver’] = ‘mysql’;

$option [‘host’] = $hostname;
$option [‘user’] = $username;
$option [‘password’] = $password;
$option [‘database’] = $database;
$option [‘prefix’] = ”;
$db = JFactory::getDBO ();
$db = & JDatabase::getInstance ($option);
if ( get_class($db) == ‘JDatabaseMySQL’ ) {
echo ‘<b><font color=”green”>OK!</font></b>’;
} else {
echo ‘<b><font color=”red”>FAILED</font></b> : ‘ . $db->message;
}

?>

Obviously I changed the database name, username and password to match my server.

I installed it under my Joolma root and sure enough a great big green OK appeared.  Now what? I asked the maintainers of the website what had changed and nothing but a Joomla component with it’s own database was disabled.  On further investigation, I found the connection to the additional database was failing.  I granted the user access over the database and Viola!  I was able to login to my Joomla site again!