Mysql - Revision history

T1t0 at 06:55, 10 October 2022

2022-10-10T06:55:06Z

← Older revision		Revision as of 06:55, 10 October 2022
Line 1:		Line 1:
	= ~~Links~~ =		= Install =

			<pre>
			# there is no more a mysql package after debian stretch
			# search for mariadb-server https://packages.debian.org/search?keywords=mariadb-server&suite=stable
			apt-get install mysql-server
			</pre>

			= Notes =

			* To connect with a password protected instance:

			--password with no arguments tell the command to ask for a password
			<pre>
			mysql --host=127.0.0.1 --port=3306 --user=mysql --password mysql
			</pre>

	* [http://dev.mysql.~~com/doc/ MySQL Reference Manuals]~~		--password=pass1234 tell the command to use this argument as password
	* [http://www.~~patrickpatoray~~.~~com/?Page~~=~~30 MySQL Dump~~/~~Restore]~~		<pre>
			mysql --host=127.0.0.1 --port=3306 --user=mysql --password=pass1234 mysql
			</pre>

	= System query =		= System query =
Line 34:		Line 51:
	</pre>		</pre>

	= Disable root password =		= Administration =

			== Disable root password ==

	<pre>		<pre>
Line 45:		Line 64:
	* see http://www.debian-administration.org/articles/442		* see http://www.debian-administration.org/articles/442

	= Create user =		== Create user ==

	<pre>		<pre>
Line 53:		Line 72:
	</pre>		</pre>

	== Create super user ==		=== Create super user ===

	<pre>		<pre>
Line 63:		Line 82:
	</pre>		</pre>

	= Create database =		== Create database ==

	<pre>		<pre>
Line 71:		Line 90:
	</pre>		</pre>

	= Grant permissons =		== Grant permissons ==

	<pre>		<pre>
Line 80:		Line 99:
	</pre>		</pre>

	= Test user =		== Test user ==

	<pre>		<pre>
Line 87:		Line 106:
	</pre>		</pre>

	= Backup and restore =		== Backup and restore ==

	== A single database ==		=== A single database ===

	* Dump		* Dump
Line 138:		Line 157:
	</pre>		</pre>

	== Backup and Recovery all databases ==		=== Backup and Recovery all databases ===

	* Backup		* Backup
Line 173:		Line 192:
	root@host:~# mysql -u root < dump.sql		root@host:~# mysql -u root < dump.sql
	</pre>		</pre>

			= Links =

			* [http://dev.mysql.com/doc/ MySQL Reference Manuals]
			* [http://www.patrickpatoray.com/?Page=30 MySQL Dump/Restore]

	[[Category:database]]		[[Category:database]]
	[[Category:mysql]]		[[Category:mysql]]

T1t0 at 16:42, 25 January 2020

2020-01-25T16:42:16Z

← Older revision		Revision as of 16:42, 25 January 2020
Line 1:		Line 1:
	== Links ==		= Links =

	* [http://dev.mysql.com/doc/ MySQL Reference Manuals]		* [http://dev.mysql.com/doc/ MySQL Reference Manuals]
	* [http://www.patrickpatoray.com/?Page=30 MySQL Dump/Restore]		* [http://www.patrickpatoray.com/?Page=30 MySQL Dump/Restore]

			= System query =

			== List users ==
	~~== System query ==~~



	=== List users ===

	<pre>		<pre>
Line 17:		Line 13:
	</pre>		</pre>

			== List databases ==

	=== List databases ===

	<pre>		<pre>
Line 26:		Line 20:
	</pre>		</pre>

			== List db tables ==

	=== List db tables ===

	<pre>		<pre>
Line 35:		Line 27:
	</pre>		</pre>

			== List table columns ==

	=== List table columns ===

	<pre>		<pre>
Line 44:		Line 34:
	</pre>		</pre>

			= Disable root password =

	== Disable root password ==

	<pre>		<pre>
Line 57:		Line 45:
	* see http://www.debian-administration.org/articles/442		* see http://www.debian-administration.org/articles/442

			= Create user =

	== Create user ==

	<pre>		<pre>
Line 67:		Line 53:
	</pre>		</pre>

			== Create super user ==

	=== Create super user ===

	<pre>		<pre>
Line 79:		Line 63:
	</pre>		</pre>

			= Create database =

	== Create database ==

	<pre>		<pre>
Line 89:		Line 71:
	</pre>		</pre>

			= Grant permissons =

	== Grant permissons ==

	<pre>		<pre>
Line 100:		Line 80:
	</pre>		</pre>

			= Test user =

	== Test user ==

	<pre>		<pre>
Line 109:		Line 87:
	</pre>		</pre>

			= Backup and restore =

			== A single database ==
	~~== Backup and restore ==~~



	=== A single database ===


	* Dump		* Dump
Line 165:		Line 138:
	</pre>		</pre>

	=== Backup and Recovery all databases ===		== Backup and Recovery all databases ==


	* Backup		* Backup
Line 201:		Line 173:
	root@host:~# mysql -u root < dump.sql		root@host:~# mysql -u root < dump.sql
	</pre>		</pre>



	[[Category:database]]		[[Category:database]]
	[[Category:mysql]]		[[Category:mysql]]

imported>T1t0 at 12:58, 5 June 2014

2014-06-05T12:58:05Z

← Older revision		Revision as of 12:58, 5 June 2014
Line 85:		Line 85:
	<pre>		<pre>
	root@host:~# mysql mysql -u root		root@host:~# mysql mysql -u root
	mysql> CREATE DATABASE ~~db_name~~ CHARACTER SET 'utf8';		mysql> CREATE DATABASE name_db CHARACTER SET 'utf8';
	mysql> \q		mysql> \q
	</pre>		</pre>
Line 95:		Line 95:
	<pre>		<pre>
	root@host:~# mysql mysql -u root		root@host:~# mysql mysql -u root
	mysql> GRANT ALL PRIVILEGES ON ~~db_name~~.* TO 'db_user' WITH GRANT OPTION;		mysql> GRANT ALL PRIVILEGES ON name_db.* TO 'db_user' WITH GRANT OPTION;
	mysql> FLUSH PRIVILEGES;		mysql> FLUSH PRIVILEGES;
	mysql> \q		mysql> \q
Line 105:		Line 105:

	<pre>		<pre>
	root@host:~# mysql ~~db_name~~ -u db_user -p		root@host:~# mysql name_db -u db_user -p
	mysql> \q		mysql> \q
	</pre>		</pre>
Line 120:		Line 120:
	* Dump		* Dump
	<pre>		<pre>
	mysqldump --single-transaction ~~db_name~~ > dbname-dump.sql		mysqldump --single-transaction name_db > dbname-dump.sql
	</pre>		</pre>
	or		or
	<pre>		<pre>
	mysqldump --single-transaction ~~db_name~~ \| gzip > dbname-dump.sql.gz		mysqldump --single-transaction name_db \| gzip > dbname-dump.sql.gz
	</pre>		</pre>

Line 139:		Line 139:
	* Restore		* Restore
	<pre>		<pre>
	mysql ~~db_name~~ < dbname-dump.sql		mysql name_db < dbname-dump.sql
	</pre>		</pre>

Line 152:		Line 152:
	# on source host		# on source host

	root@host_src:~# mysqldump -u root --single-transaction ~~db_name~~ > dbname-dump.sql		root@host_src:~# mysqldump -u root --single-transaction name_db > dbname-dump.sql

	# on destination host		# on destination host
Line 158:		Line 158:
	root@host_dst:~# mysql mysql -u root		root@host_dst:~# mysql mysql -u root
	mysql> CREATE USER 'db_user' IDENTIFIED BY 'db_pass';		mysql> CREATE USER 'db_user' IDENTIFIED BY 'db_pass';
	mysql> CREATE DATABASE ~~db_name~~ CHARACTER SET 'utf8'		mysql> CREATE DATABASE name_db CHARACTER SET 'utf8'
	mysql> GRANT ALL PRIVILEGES ON ~~db_name~~.* TO 'db_user' WITH GRANT OPTION;		mysql> GRANT ALL PRIVILEGES ON name_db.* TO 'db_user' WITH GRANT OPTION;
	mysql> flush privileges;		mysql> flush privileges;
	mysql> \q		mysql> \q
	root@host_dst:~# mysql ~~db_name~~ < dbname-dump.sql		root@host_dst:~# mysql name_db < dbname-dump.sql
	</pre>		</pre>

imported>T1t0 at 16:33, 13 January 2013

2013-01-13T16:33:46Z

← Older revision		Revision as of 16:33, 13 January 2013
Line 56:		Line 56:

	* see http://www.debian-administration.org/articles/442		* see http://www.debian-administration.org/articles/442



	== Create user ==		== Create user ==
Line 62:		Line 64:
	root@host:~# mysql mysql -u root		root@host:~# mysql mysql -u root
	mysql> CREATE USER 'db_user' IDENTIFIED BY 'db_pass';		mysql> CREATE USER 'db_user' IDENTIFIED BY 'db_pass';
			mysql> \q
			</pre>



			=== Create super user ===

			<pre>
			root@host:~# mysql mysql -u root
			mysql> CREATE USER 'superuser' IDENTIFIED BY 'superuser';
			mysql> GRANT ALL PRIVILEGES ON . TO 'superuser'@'%' WITH GRANT OPTION;
			mysql> FLUSH PRIVILEGES;
	mysql> \q		mysql> \q
	</pre>		</pre>
Line 74:		Line 88:
	mysql> \q		mysql> \q
	</pre>		</pre>



	== Grant permissons ==		== Grant permissons ==
Line 80:		Line 96:
	root@host:~# mysql mysql -u root		root@host:~# mysql mysql -u root
	mysql> GRANT ALL PRIVILEGES ON db_name.* TO 'db_user' WITH GRANT OPTION;		mysql> GRANT ALL PRIVILEGES ON db_name.* TO 'db_user' WITH GRANT OPTION;
	mysql> ~~flush privileges~~;		mysql> FLUSH PRIVILEGES;
	mysql> \q		mysql> \q
	</pre>		</pre>

imported>T1t0 at 19:37, 19 March 2012

2012-03-19T19:37:47Z

← Older revision		Revision as of 19:37, 19 March 2012
Line 157:		Line 157:

	<pre>		<pre>
	~~shell>~~ mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql		root@host:~# mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql
	</pre>		</pre>

Line 169:		Line 169:

	<pre>		<pre>
	~~shell>~~ mysqldump -u root --single-transaction --all-databases --flush-logs --lock-tables --flush-privileges \| gzip > alldatabase_dump.sql.gz		root@host:~# mysqldump -u root --single-transaction --all-databases --flush-logs --lock-tables --flush-privileges \| gzip > alldatabase_dump.sql.gz
	</pre>		</pre>

Line 183:		Line 183:

	<pre>		<pre>
	~~shell>~~ mysql -u root < dump.sql		root@host:~# mysql -u root < dump.sql
	</pre>		</pre>

imported>T1t0: /* Disable root password */

2012-03-14T22:05:10Z

Disable root password

← Older revision		Revision as of 22:05, 14 March 2012
Line 55:		Line 55:
	</pre>		</pre>

			* see http://www.debian-administration.org/articles/442

	== Create user ==		== Create user ==

imported>T1t0 at 12:58, 7 February 2012

2012-02-07T12:58:55Z

← Older revision		Revision as of 12:58, 7 February 2012
Line 185:		Line 185:
	shell> mysql -u root < dump.sql		shell> mysql -u root < dump.sql
	</pre>		</pre>



			[[Category:database]]
			[[Category:mysql]]

imported>T1t0 at 11:50, 6 February 2012

2012-02-06T11:50:04Z

← Older revision		Revision as of 11:50, 6 February 2012
Line 6:		Line 6:


	== List users ==		== System query ==



			=== List users ===

	<pre>		<pre>
Line 15:		Line 19:


	== List databases ==		=== List databases ===

	<pre>		<pre>
	root@host:~# mysql -u root		root@host:~# mysql -u root
	mysql> show databases;		mysql> select schema_name from information_schema.schemata; # or show databases;
	</pre>		</pre>

	or

			=== List db tables ===

			<pre>
			root@host:~# mysql -u root
			mysql> show tables;
			</pre>



			=== List table columns ===

	<pre>		<pre>
	root@host:~# mysql -u root		root@host:~# mysql -u root
	mysql> ~~select schema_name~~ from ~~information_schema.schemata~~;		mysql> show columns from db_table;
	</pre>		</pre>

imported>T1t0 at 11:17, 6 February 2012

2012-02-06T11:17:17Z

@@ Line 3: / Line 3: @@
 * [http://dev.mysql.com/doc/ MySQL Reference Manuals]
 * [http://www.patrickpatoray.com/?Page=30 MySQL Dump/Restore]
@@ Line 11: / Line 20: @@
 root@host:~# mysql -u root
 mysql> show databases;
 </pre>

imported>T1t0 at 18:10, 12 December 2011

2011-12-12T18:10:48Z

← Older revision		Revision as of 18:10, 12 December 2011
Line 105:		Line 105:
	# on source host		# on source host

	root@host_src:~# mysqldump --single-transaction db_name > dbname-dump.sql		root@host_src:~# mysqldump -u root --single-transaction db_name > dbname-dump.sql

	# on destination host		# on destination host
Line 138:		Line 138:

	<pre>		<pre>
	shell> mysqldump --single-transaction --all-databases --flush-logs --lock-tables --flush-privileges \| gzip > alldatabase_dump.sql.gz		shell> mysqldump -u root --single-transaction --all-databases --flush-logs --lock-tables --flush-privileges \| gzip > alldatabase_dump.sql.gz
	</pre>		</pre>

Line 152:		Line 152:

	<pre>		<pre>
	shell> mysql < dump.sql		shell> mysql -u root < dump.sql
	</pre>		</pre>