MySQL - Revision history

Goldbolt: /* Mass MySQL database repair */

2020-03-11T11:10:34Z

‎Mass MySQL database repair

Goldbolt at 15:12, 7 March 2020

2020-03-07T15:12:30Z

Goldbolt at 14:45, 7 March 2020

2020-03-07T14:45:16Z

Goldbolt at 14:37, 7 March 2020

2020-03-07T14:37:15Z

Goldbolt at 14:36, 7 March 2020

2020-03-07T14:36:36Z

Goldbolt at 14:26, 7 March 2020

2020-03-07T14:26:09Z

Show changes

Goldbolt at 14:16, 7 March 2020

2020-03-07T14:16:32Z

Goldbolt at 17:14, 6 August 2019

2019-08-06T17:14:25Z

Goldbolt at 16:34, 6 August 2019

2019-08-06T16:34:56Z

Goldbolt at 13:51, 3 January 2019

2019-01-03T13:51:22Z

@@ Line 221: / Line 221: @@
 Repair all MyISAM databases:
      for i in $(find /var/lib/mysql/ -name '*.MYI'); do myisamchk -r -f $i; done
 =MySQL optimizations=

← Older revision		Revision as of 15:12, 7 March 2020
Line 1:		Line 1:
		+	[[File:MySQL.png\|thumb]]
	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 (/ˌ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.

← Older revision		Revision as of 14:45, 7 March 2020
Line 1:		Line 1:
−	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 (/ˌ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.		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.

← Older revision		Revision as of 14:37, 7 March 2020
Line 378:		Line 378:
	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:		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:

−	slowqueryparser.py -u ~~userna5~~ \| mysqldumpslow - > ~~~userna5~~/slow-query-report.txt	+	slowqueryparser.py -u username \| mysqldumpslow - > ~username/slow-query-report.txt

← Older revision		Revision as of 14:36, 7 March 2020
Line 247:		Line 247:

	Be aware that incorrectly changing to recommended setting may cause OoM kills or other undesirable behavior.		Be aware that incorrectly changing to recommended setting may cause OoM kills or other undesirable behavior.
		+
		+	=Troubleshooting=
		+	'''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'
		+
		+	Otherwise:
		+
		+	Add this line to /etc/my.cnf and restart MySQL:
		+
		+	skip-grant-tables
		+
		+
		+	Follow these steps to reset the MySQL root password:
		+
		+	mysql -u root
		+	mysql> FLUSH PRIVILEGES;
		+	mysql> GRANT ALL PRIVILEGES ON . TO root@localhost IDENTIFIED BY 'ourmysqlpassword' WITH GRANT OPTION;
		+	mysql> FLUSH PRIVILEGES;
		+	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.
		+
		+	http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#option_mysqld_ignore-builtin-innodb
		+
		+	You can add this to your my.cnf:
		+
		+	[mysqld]
		+	innodb=OFF
		+	ignore-builtin-innodb
		+	skip-innodb
		+	default-storage-engine=myisam
		+	default-tmp-storage-engine=myisam
		+
		+	'''Missing libmysqlclient.so.14'''
		+
		+	error while loading shared libraries: libmysqlclient.so.14: cannot open shared object file: No such file or directory
		+
		+
		+	Run this command:
		+
		+	cp /usr/lib/mysql/libmysqlclient.so.14 /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: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
		+	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 'slowqueryparser.py' 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 \| slowqueryparser.py
		+
		+	As with the general query parser, per-user output can be extracted with the '--user=' option:
		+
		+	tail -50000 /var/log/slowqueries \| slowqueryparser.py --user=username --output=/home/username/slowqueries.log
		+
		+	Full help output:
		+
		+	usage: slowqueryparser.py [options] [filename]
		+
		+	[filename] is optional and defaults to stdin.
		+
		+	options:
		+	-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>
		+	options:
		+	-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:
		+
		+	slowqueryparser.py -u userna5 \| mysqldumpslow - > ~userna5/slow-query-report.txt

← Older revision		Revision as of 14:16, 7 March 2020
Line 34:		Line 34:
	Repair all MyISAM databases:		Repair all MyISAM databases:
	for i in $(find /var/lib/mysql/ -name '*.MYI'); do myisamchk -r -f $i; done		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.

← Older revision		Revision as of 17:14, 6 August 2019
Line 21:		Line 21:
	mysqladmin -u username -p create database1		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=		=MySQL recovery mode=

← Older revision		Revision as of 16:34, 6 August 2019
Line 25:		Line 25:
	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.		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 ;		* 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

← Older revision		Revision as of 13:51, 3 January 2019
Line 1:		Line 1:
−	~~<strong>MySQL</strong>~~
	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 (/ˌ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.