MySQL - Revision history

Rootadminacc: /* MySQLdump */

2019-05-17T08:57:44Z

MySQLdump

← Older revision		Revision as of 09:57, 17 May 2019
Line 443:		Line 443:
	systemctl restart mysql		systemctl restart mysql
	systemctl restart mysqld</pre>		systemctl restart mysqld</pre>

			If this doesn't work you may need this fix: http://www.lampdocs.com/couldnt-execute-show-variables-like-gtid_mode-table-performance_schema-session_variables-doesnt-exist-1146-how-to-fix/

	=== FreeBSD server ===		=== FreeBSD server ===

2019-05-17T08:57:07Z

MySQLdump

← Older revision		Revision as of 09:57, 17 May 2019
Line 436:		Line 436:

	<pre>mysql_upgrade -u root -p --force</pre>		<pre>mysql_upgrade -u root -p --force</pre>

			Then restart mysql:

			<pre>service mysql restart
			service mysqld restart
			systemctl restart mysql
			systemctl restart mysqld</pre>

	=== FreeBSD server ===		=== FreeBSD server ===

2019-05-17T08:55:07Z

MySQLdump

← Older revision		Revision as of 09:55, 17 May 2019
Line 427:		Line 427:
	== MySQLdump ==		== MySQLdump ==

	mysqldump -u username -p database_to_backup > backup_name.sql		<pre>mysqldump -u username -p database_to_backup > backup_name.sql</pre>

			If you get the following error:

			<pre>mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'': Table 'performance_schema.session_variables' doesn't exist (1146)</pre>

			Then run the following command:

			<pre>mysql_upgrade -u root -p --force</pre>

	=== FreeBSD server ===		=== FreeBSD server ===

2018-07-27T09:39:10Z

MySQLdump

← Older revision		Revision as of 10:39, 27 July 2018
Line 426:		Line 426:

	== MySQLdump ==		== MySQLdump ==

			mysqldump -u username -p database_to_backup > backup_name.sql

	=== FreeBSD server ===		=== FreeBSD server ===

2018-07-27T09:38:41Z

← Older revision		Revision as of 10:38, 27 July 2018
Line 1:		Line 1:
	Note: MySQL on Fedora and CentOS can be called mysqld		Note: MySQL on Fedora and CentOS can be called mysqld

			== Backup a database ==

			See http://serverkb.co.uk/wiki/MySQL#MySQLdump

	== Before making changes... ==		== Before making changes... ==

2016-04-21T15:54:14Z

Reset root password for MySQL

← Older revision		Revision as of 16:54, 21 April 2016
Line 581:		Line 581:
	== Reset root password for MySQL ==		== Reset root password for MySQL ==

	Quick way if you already know the password:		Quick way if you already know the password, outside of MySQL do:

	<pre>mysqladmin -u root -p'OldPassword' password NewPassword</pre>		<pre>mysqladmin -u root -p'OldPassword' password NewPassword</pre>

	~~IfF~~ you don't know the current password:		Inside MySQL:

			<pre>SET PASSWORD FOR 'root'@'localhost'=PASSWORD('MyNewPass');</pre>

			If you don't know the current password:

	=== [http://kwatog.com/blog/reset-mysql-root-password-in-centos CentOS] ===		=== [http://kwatog.com/blog/reset-mysql-root-password-in-centos CentOS] ===

2016-04-09T20:56:45Z

Show users permissions

← Older revision		Revision as of 21:56, 9 April 2016
Line 704:		Line 704:

	SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user;		SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user;

			http://dbadiaries.com/no-mysql-show-users-how-to-list-mysql-user-accounts-and-their-privileges

	== Show over 1000 rows ==		== Show over 1000 rows ==

2016-04-09T20:56:24Z

Show users permissions

← Older revision		Revision as of 21:56, 9 April 2016
Line 700:		Line 700:

	SELECT User FROM mysql.user;		SELECT User FROM mysql.user;

			SELECT * FROM mysql.user;

			SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user;

	== Show over 1000 rows ==		== Show over 1000 rows ==

2016-04-09T20:36:56Z

Show over 1000 rows

@@ Line 688: / Line 688: @@
 mysql -p<br>
 yourpassword
 == Show over 1000 rows ==

2015-07-16T21:39:09Z

Manually create database and user (Unix)

← Older revision		Revision as of 22:39, 16 July 2015
Line 415:		Line 415:
	Then test the connection works to the database with that username and password:		Then test the connection works to the database with that username and password:

	<pre> mysql -h localhost -u YourUser -DNewDatabase -p</pre>		<pre>mysql -h localhost -u YourUser -DNewDatabase -p</pre>

	== Monitor queries ==		== Monitor queries ==