<syntaxhighlight lang="SQL"> CREATE TABLE user ( ID INT AUTO_INCREMENT PRIMARY_KEY, username VARCHAR(64), hash VARCHAR(512) ); </syntaxhighlight>

<syntaxhighlight lang="SQL">

The column references are optional - if included, the VALUES will be inserted into the specified columns in order. If no column references are provided, the VALUES will simply be inserted from the first column onwards. Relating to our last example, the query we would use to add a new entry to our table would be something like:

In this case, we do not insert anything into the first value - ID - as PRIMARY KEY will automatically generate its value. The next value, username, we fill with a value, and for the final value we use MySQL's native md5 function to create a hashed password.

Once we've done this, our table will look a little something like this:

The output from MySQL would be like this:

  mysql> SELECT * FROM user;
  +----+-----------+----------------------------------+
  | ID | username  | hash                             |
  +----+-----------+----------------------------------+
  |  1 | Selketraz | 05a208028929fd77cfb5b08096a837df |
  +----+-----------+----------------------------------+
  1 row in set (0.00 sec)

If we wanted to change one of these values, we would use the UPDATE query instead. For example, say Selketraz forgets her password and needs to have it reset. We would simply alter her entry in the database with UPDATE to set her password to a default one.

This employs the WHERE statement to select only certain rows - in effect, it says FOR every row in the entire table: IF username="Selketraz", THEN change the password hash to 'default1%'.

Finally, entry deletion. In order to delete an entry from a table, simply use the DELETE query, which is structured similarly to UPDATE:

Troubleshooting

InnoDB Disabled

Run this command in SSH as root to show the current engines:

 mysql -e "show engines;"

If you run a mysqcheck and get something like the following on a number of tables:

Error    : Incorrect information in file: './halp_blah/example_innodb.frm'
error    : Corrupt

The tables are not actually corrupt, they are just InnoDB tables and InnoDB is disabled.

Locked Tables

http://dev.mysql.com/doc/refman/5.0/en/table-locking.html

If you see MANY locks on a table (or tables, specifically MyISAM not InnoDB), the option low_priority_updates may help. If it is enabled, selects will get higher priority than updates and waiting updates will not stall all new selects to the table until they are executed.

low_priority_updates = 1 

Post-Upgrade - MySQL does not start

If you get the following error message why trying to start MySQL 5.x after an upgrade:

Starting MySQL
Couldn't find MySQL manager or server [FAILED]

Then edit /etc/my.cnf and comment out the following line:

basedir=/var/lib

Then restart MySQL.

MySQL Top Resources Script

<syntaxhighlight lang="bash"> mysqladmin processlist | cut -d"|" -f3 | sort | uniq -c | sort -n | tail -n 8 </syntaxhighlight>

MySQL Datadir Migration

 mkdir -p /home/mysql
 chown mysql:mysql /home/mysql
 rsync -avHl /var/lib/mysql/ /home/mysql/
 /etc/init.d/{crond,mysql} stop
 rsync -avHl /var/lib/mysql/ /home/mysql/
 mv /var/lib/mysql /var/lib/mysql.bak
 ln -sf /home/mysql /var/lib/mysql

If /var is full

 rsync -avHlT /home/rtmp$$ /var/lib/mysql/ /home/mysql/

Post-migration

 [mysqld]
 datadir=/home/mysql
 #basedir=/var/lib/mysql

It should then be safe to restart MySQL:

 /etc/init.d/{crond,mysql} start
 rm -rf /var/lib/mysql.bak
 rm -f /home/mysql/*.pid
 touch /home/mysql/$(hostname).pid

Database Repair

Run this first to determine which tables are corrupted:

 mysqlcheck -Asc

Repair Corrupted Database (REPAIR)

 mysqlcheck -r <database name>

Repair Corrupted Database (With FRM)

Repair ALL Databases and Reindex Tables

<syntaxhighlight lang="bash"> /etc/init.d/{crond,mysql} stop cd /var/lib/mysql/ find . -type f -name '*.MYI' -exec myisamchk -fUp '{}' \; /etc/init.d/{crond,mysql} start </syntaxhighlight>

InnoDB Thread Issues

This is most likely a ulimit issue in /etc/init.d/mysql. You can usually find a 'ulimit -s' somewhere in there, and replace the number after it with a number that works for you. If not, add it near the top.

 ulimit -s 8192

Slow Query Log

touch /var/lib/mysql/slow.log
chown mysql. /var/lib/mysql/slow.log

In the my.cnf file under the mysqld section add this:

log-slow-queries=/var/lib/mysql/slow.log

Then restart mysql and you have a slow query log.

If you want to specify the number of seconds that indicates a long or slow query, use this line in /etc/my.cnf :

long_query_time = 5

MySQL Commands

• Full list here: http://www.pantz.org/software/mysql/mysqlcommands.html

Create a backup of a database

 <syntaxhighlight lang="bash">mysqldump <database> > <database>.sql</syntaxhighlight>

Restore an entire database

 <syntaxhighlight lang="bash">mysqldump <database> > <database>.backup.sql # Always dump the old databse if it is still there, in case we need to bring it back.
 mysql <database> < <sql-file>.sql</syntaxhighlight>

Restore a single table

 <syntaxhighlight lang="bash">mysqldump <database> > <database>.backup.sql # Backup database, just in case.
 cat <database-backup>.sql | sed -n '/-- Table structure for table `<table_name>`/,/-- Table/{ /^--.*$/d;p }' > <table_name>_table.sql
 mysql <database> < <table_name>_table.sql</syntaxhighlight>

Dump mysql user privileges

 <syntaxhighlight lang="bash">mysql -B -N -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR , user, @, host, ;') AS query FROM mysql.user" | mysql $@ |   sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'</syntaxhighlight>

Restore a single database from a full dump

 <syntaxhighlight lang="bash">mysql --one-database database_name < fulldump.sql</syntaxhighlight>

Extract sql for a single database from a full dump

<syntaxhighlight lang="bash">sed -n '/^-- Current Database: `singledb`/,/^-- Current Database: `/p' fulldump.sql > singledb.sql</syntaxhighlight>

Dumping a database in Plesk

 <syntaxhighlight lang="bash">mysqldump -u admin -p database > .sql</syntaxhighlight>

Downgrading

Downgrading MySQL

• Grab a list of the currently installed mysql packages on your server

 rpm -qa | grep -i mysql-

• Make Backups

 mkdir /home/sqlbackup/
 for i in $(mysql -e "show databases;" | sed 's/Database//') ; do mysqldump $i > /home/sqlbackup/$i.sql ; done

cPanel users

If you are running cPanel on your server, Edit /var/cpanel/cpanel.config/, and set mysql-version to your desired MAJ.MIN (eg. 5.0) and

 rm -f /root/.cpanel/datastore/*_mysql*
 

You can run /scripts/mysqlup --force on cPanel servers to upgrade MySQL automatically, but you'll still need to run EasyApache to use the new mysql shared libraries, since this script wipes the old ones out, breaking php-mysql.

• Check to see that the new version is installed

 rpm -qa | grep -i mysql-

• Stop MySQL and CRON

killall -9 crond
# on cPanel servers, killall -9 tailwatchd
/etc/init.d/mysql stop

• Move MySQL install to timestamped directory

 mv /var/lib/mysql /home/mysql.$(date +%s)

• Reinstall

 mkdir /var/lib/mysql
 mysql_install_db
 mv /root/.my.cnf /root/.my.cnf.old
 service mysql restart

• Import everything

 cd /home/temp/sqldump
 for i in *.sql ; do mysqladmin create ${i%.*} ; done
 for i in *.sql ; do mysql ${i%.*} < $i ; done ; done
 mv /root/.my.cnf.old /root/.my.cnf
 service mysql restart

cPanel users will want to run EasyApache in SSH via /scripts/easyapache (recommended in screen)

Upgrading MySQL

 mysqlcheck -r --all-databases
 cd /backup
 mkdir mysqldumps
 cd /backup/mysqldumps
 for i in $(mysql -e "show databases;" | cut -d ' ' -f2 | grep -v Database); do \
   mysqldump $i > $i.sql; \
 done