From TBP Wiki
Jump to: navigation, search

MySQL (/ˌmaɪˌɛsˌkjuːˈɛl/ "My S-Q-L") is an open source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter, and "SQL", the abbreviation for Structured Query Language. The MySQL development project has made its source code available under the terms of the GNU General Public License, as well as under a variety of proprietary agreements. MySQL was owned and sponsored by a single for-profit firm, the Swedish company MySQL AB, now owned by Oracle Corporation. For proprietary use, several paid editions are available, and offer additional functionality.

MySQL is a central component of the LAMP open-source web application software stack (and other "AMP" stacks). LAMP is an acronym for "Linux, Apache, MySQL, Perl/PHP/Python". Applications that use the MySQL database include: TYPO3, MODx, Joomla, WordPress, Simple Machines Forum, phpBB, MyBB, and Drupal. MySQL is also used in many high-profile, large-scale websites, including Google (though not for searches), Facebook, Twitter, Flickr, and YouTube.

Service Control

   mysql  {start|stop|restart|reload|force-reload|status} 

The mysqladmin utility is also used for MySQL management. You can find a list of mysqladmin options by invoking the utility without any arguments:


Basic Commands

These command assume you are running them as root. If not, you need to specify a user with '-u user_name -p' Logging In

Log into MySQL prompt as the user:

    mysql -u vnessa5_test -p

Log into MySQL prompt as root:



    mysql -u root -p

Importing/Exporting Databases

Importing Databases

phpMyAdmin is unable to import databases larger than 50M unless otherwise set within the settings. You can import large database from the command line so long as you have a sql dump and the username and password for the database on the server.

Import a database as the user (replace username with their username or the username from their database credentials - it will prompt you for the password):

   mysql -p -u username  db_name < file.sql

Importing the database as root:

   mysql db_name < file.sql

You may have to check the first few lines of the file to make sure there are no 'CREATE DATABASE' and 'USE DATABASE' commands in the file and, if there are, remove of comment them out as another database will be created.

Importing multiple databases:

When running this command as root, check the 'CREATE DATABASE' and 'USE DATABASE' commands to ensure the correct database names are being used:

   mysql -u root < file.sql

Warning: this is not recommended for security reasons. If you cannot review the entire content of the file, you will not know if you are running malicious commands! For example, a user could easily add 'drop database user' and remove all users from mysql. In short avoid doing this - import using individual databases.

Exporting a database

Dump a Database (on other servers):

   mysqldump -Q --add-drop-table db_name > file.sql

Dump a Database, 4.0 compatible (works on 4.1 or higher servers):

   mysqldump --compatible=mysql40 --add-drop-table --quote-name db_name > file.sql

Dump Multiple Databases:

   mysqldump -Q --add-drop-table --databases db_name1 db_name2 > file.sql

Managing Databases and Tables

Show Databases: (will only show databases the user has access to. Root has all.)

   show databases;

Drop a whole database:

   drop database databasename;

Create a database: *only the root mysql user can use this command

   create database databasename;

Select a database to work on:

   use databasename;

Drop a specific table:

   drop table wp_options;

Admin Commands

Process Management

Show all MySQL Processes:

   show full processlist;

Kill a process (use 'show full processlist' to get PID)

   kill 9843;

To view various mysql stats

   mysqladmin version

To view current queries

   mysqladmin processlist

User & Privilege Management

Grant superuser privileges. Be careful with this.

   GRANT ALL PRIVILEGES ON *.* TO user_name@localhost;

Grant superuser privileges to a user for just this database.

   GRANT USAGE ON *.* TO 'user'@'host' GRANT Select, Insert, Update, Delete, Create, Drop ON `database`.* TO 'user'@'host' FLUSH PRIVILEGES;

Have privileges take effect without having to restart.


List of Privileges:

    RELOAD : flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload

Key Directories, Files, and Scripts

   MySQL root folder (contains all databases, named per folder)


   MySQL process ID file


   MySQL error log


   Slow queries log, if enabled in my.cnf


   Various MySQL logs as specified in my.cnf (varies)


   MySQL main configuration file


   A Unix utility provided by MySQL to translate error codes

cPanel Scripts


   Fixes common MySQL problems (mysql.sock, /var/lib/mysql ownership/perms, stable PID)

/scripts/mysqlup [--force]

   Upgrades/reinstalls MySQL (version specified in /var/cpanel/cpanel.conf)


   Adds a MySQL user


   Removed a MySQL user's privileges


   Deletes a MySQL database


   Shows MySQL user privilege table (warning: not filtered = a lot of results)


   contains options for securing MySQL


   Suspends a MySQL user


   Unsuspends a MySQL user


   Checks for MySQL connections, re-establishes mysql.sock


   Updates quotas for all MySQL users

Dumping and Restoring Databases

To dump a database use:

   mysqldump -u username -p database1 > dump-lbry.sql

To restore that database:

   mysql -u username -p database1 < dump-lbry.sql

To destroy a database from CLI:

   mysqladmin -u username -p drop database1

To create a database from CLI:

   mysqladmin -u username -p create database1

Perform backup of all mysql databases

   mkdir /root/dbbackups; touch /root/dbbackups/list; for db in $( mysql -e 'show databases' | grep -v "Database\|information_schema" | awk '{print $1}' ) ; do mysqldump --add-drop-table $db > /root/dbbackups $db.sql && echo $db >> list; done

MySQL recovery mode

Start with "1" and go to "3"; try not to go above "3" or you will start to see dropped tables and further corruption. Use above "3" only with a backup.

  • echo "innodb_force_recovery = 1" >> /etc/my.cnf; /scripts/restartsrv_mysql ;

Mass MySQL database repair

Repair all MySQL databases:

   mysqlcheck -reA

Repair all MyISAM databases:

   for i in $(find /var/lib/mysql/ -name '*.MYI'); do myisamchk -r -f $i; done

Really dumb way to do it several times:

   mysqlcheck -reA && for db in $(for i in `ls /var/cpanel/users | grep -v "./"`; do /opt/dedrads/lil-cpanel $i mysql list dbs; done); do mysqlcheck -r $db; mysqlcheck -o $db; done && for i in $(find /var/lib/mysql/ -name '*.MYI'); do myisamchk -r -f $i; done

MySQL optimizations

Query Cache

in /etc/my.cnf:


These settings will enable caching for queries except for those that are specifically marked as no caching enabled. See for adjusting values.

More information is available here: MySql:_my.cnf_settings#Enabling_the_Query_Cache

Additionally, a tool exists that can provide further insight into tuning MySQLd:

You can obtain this tool by running:

   wget -O

You start the tool by running:


Be aware that incorrectly changing to recommended setting may cause OoM kills or other undesirable behavior.


InnoDB Crashes

InnoDB crashes can be very complex to resolve.

First, try setting InnoDB to recovery mode. Edit /etc/my.cnf and add:

   innodb_force_recovery = 1

Then restart MySQL. If it does not started up, keep increasing the recovery level up to 3 until it does start. If it doesn't start at this point, get a developer.

Once it does start, reverse the change you made and restart again normally. If InnoDB does not initialize after doing this, you may be dealing with a more complex issue that might require re-importing InnoDB data.

Root Login Failure

If this is a cPanel server, simply run:

   /scripts/mysqlpass root 'password'


Add this line to /etc/my.cnf and restart MySQL:


Follow these steps to reset the MySQL root password:

   mysql -u root
   mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'ourmysqlpassword' WITH GRANT OPTION;
   mysql> exit;
   service mysql restart
Remove the line you added to my.cnf and restart again. 

Disable innodb on MySQL 5.6

If you're using MySQL 5.6+ and want to disable InnoDB, don't forget "--default-tmp-storage" or it won't work.

To disable InnoDB, use --innodb=OFF or --skip-innodb. In this case, because the default storage engine is InnoDB, the server will not start unless you also use --default-storage-engine and --default-tmp-storage-engine to set the default to some other engine for both permanent and TEMPORARY tables.

You can add this to your my.cnf:



error while loading shared libraries: cannot open shared object file: No such file or directory

Run this command:

   cp /usr/lib/mysql/ /usr/lib

InnoDB Errors

If you see the following error while attempting to start (or restart) MySQL

   InnoDB: corruption in the InnoDB tablespace. Please refer to
   InnoDB: about forcing recovery.

Add the following line to my.cnf:

   innodb_force_recovery = 4

If you see a large number of databases with the following error:

   Incorrect information in file: './database/table.frm'

It means the innodb engine has locked up and crashed. You need to comment out the innodb lines in /etc/my.cnf, restart mysql, uncomment the innodb lines again, restart mysql.

Slow queries

The slow query parser tool can be used to parse the slow query log. Running '' by itself will return a list sorted by number of queries and including total time, total lock time, total rows sent, and total rows received. It is recommended that you pipe only the last 50k lines to this script, since the slow query log is infrequently rotated:

   tail -50000 /var/log/slowqueries |

As with the general query parser, per-user output can be extracted with the '--user=' option:

   tail -50000 /var/log/slowqueries | --user=username --output=/home/username/slowqueries.log

Full help output:

   usage: [options] [filename]

[filename] is optional and defaults to stdin.

    -h, --help            show this help message and exit
    -v, --verbose         Print info on stderr (default: True)
    -q, --quiet           Suppress stderr output
    -o FILE, --output=FILE
                          Write output to FILE (default: stdout)
    -u USER, --user=USER  Output USER's queries instead of tallys
    -a, --average         Print averages per query instead of totals

mysqldumpslow parses MySQL slow query log files and prints a summary of their contents. Normally, mysqldumpslow groups queries that are similar except for the particular values of number and string data values. It abstracts these values to N and S when displaying summary output. The -a and -n options can be used to modify value abstracting behaviour.

   usage: mysqldumpslow <path to slowquerylog>
     -a                Do not abstract all numbers to N and strings to ´S´.
     -g pattern        Consider only queries that match the (grep-style) pattern.
     -l                Do not subtract lock time from total time.
     -n N              Abstract numbers with at least N digits within names.
     -s sort_type      How to sort the output. The value of sort_type should be chosen from the following list:
             ·   t, at: Sort by query time or average query time
             ·   l, al: Sort by lock time or average lock time
             ·   r, ar: Sort by rows sent or average rows sent
             ·   c: Sort by count
                 By default, mysqldumpslow sorts by average query time (equivalent to -s at).

To parse the default slow query log:

   mysqldumpslow /var/log/slowqueries 

You can also pass data to it via stdin by passing '-' as the log name. You may also find it beneficial to use this tool in tandem with the slowqueryparser to provide the customer with an easy to understand report of their slow query activity: -u username | mysqldumpslow - > ~username/slow-query-report.txt