MySQL

MySQL (/ˌmaɪˌɛsˌkjuːˈɛl/ "My S-Q-L")[5] 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.

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

MySQL optimizations

Query Cache

in /etc/my.cnf:

   query_cache_size=16M
   query_cache_type=1

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

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

MySQLtuner.pl

Additionally, a tool exists that can provide further insight into tuning MySQLd: https://github.com/major/MySQLTuner-perl

You can obtain this tool by running:

   wget http://mysqltuner.pl -O mysqltuner.pl

You start the tool by running:

   perl mysqltuner.pl

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