Mysql - Historial de revisió

Marti: /* Repair */

2023-06-03T10:03:49Z

Repair

← Versió més antiga		Revisió del 10:03, 3 juny 2023
Línia 257:		Línia 257:
	If those are not MyISAM tables try Mysqlcheck:		If those are not MyISAM tables try Mysqlcheck:

	mysqlcheck --all-databases -r #repair databases		mysqlcheck --all-databases -p -r #repair databases
	mysqlcheck --all-databases -a #analyze databases		mysqlcheck --all-databases -p -a #analyze databases
	mysqlcheck --all-databases -o #optimize databases		mysqlcheck --all-databases -p -o #optimize databases

2023-05-28T13:19:04Z

Basics

← Versió més antiga		Revisió del 13:19, 28 maig 2023
Línia 1:		Línia 1:
	==~~Basics~~==		==Basic examples==

	Some examples: (to be completed)		Some examples: (to be completed)

2023-05-28T13:18:21Z

← Versió més antiga		Revisió del 13:18, 28 maig 2023
Línia 1:		Línia 1:
			==Basics==

	Some examples: (to be completed)		Some examples: (to be completed)

2023-05-28T13:17:45Z

← Versió més antiga		Revisió del 13:17, 28 maig 2023
Línia 20:		Línia 20:
	SELECT TABLE_SCHEMA AS `Database`, TABLE_NAME AS `Table`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)` FROM information_schema.TABLES ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;		SELECT TABLE_SCHEMA AS `Database`, TABLE_NAME AS `Table`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)` FROM information_schema.TABLES ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

	Clean Prestashop DB:		==Clean Prestashop DB:==

			use prestashopdafyn;
	TRUNCATE TABLE psdafyn_connections;		TRUNCATE TABLE psdafyn_connections;
			TRUNCATE TABLE psdafyn_connections_source;
	TRUNCATE TABLE psdafyn_connections_page;		TRUNCATE TABLE psdafyn_connections_page;
	TRUNCATE TABLE ~~psdafyn_guest~~;		TRUNCATE TABLE psdafyn_pagenotfound;
			TRUNCATE TABLE ps_statssearch;
			# despres cal anar a modul database optimisation https://filateliatrafalgar.com/admin2/index.php?controller=AdminModules&token=24c4d2cab9cd70a42db26d7c70bd8808&configure=dboptimization&tab_module=administration&module_name=dboptimization i clicar a clean de "unwanted carts" i "guests"

			# https://prestacraft.com/clearing-unnecessary-data-from-prestashop-database/#
			# Some people recommend to clear also ps_guest table but I would not do that. Why? Because guests infos are located in more tables, so those informations (based on guests IDs) would not have connection to specific guests (when you would clear main guest table).
			# --> millor fer-ho amb el modul

2022-02-12T02:28:49Z

← Versió més antiga	Revisió del 02:28, 12 feb 2022
Línia 16:	Línia 16:


		List all tables and show their sizes in MB (useful to troubleshoot prestashop):

		SELECT TABLE_SCHEMA AS `Database`, TABLE_NAME AS `Table`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)` FROM information_schema.TABLES ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

		Clean Prestashop DB:
		TRUNCATE TABLE psdafyn_connections;
		TRUNCATE TABLE psdafyn_connections_page;
		TRUNCATE TABLE psdafyn_guest;

2018-06-15T20:32:13Z

← Versió més antiga		Revisió del 20:32, 15 juny 2018
Línia 209:		Línia 209:
	Més info:		Més info:
	http://www.pantz.org/software/mysql/mysqlcommands.html		http://www.pantz.org/software/mysql/mysqlcommands.html




			== Repair ==
			Info from: https://www.vps.net/community/knowledgebase/display/SA/How+To+Repair+A+MySQL+Database


			Although, Mysqlcheck and Myisamchk are similar in purpose, there are some essential differences. Mysqlcheck as well as Myisamchk can check, repair and analyze MyISAM tables. Mysqlcheck can also check InnoDB tables.

			Log in as ‘root’ via SSH and:

			Shows you if any need repair:
			myisamchk --check /var/lib/mysql//.MYI


			Then try ‘safe-recover’ first:
			myisamchk --safe-recover /var/lib/mysql//.MYI


			and, if neither that nor this works:
			myisamchk --recover /var/lib/mysql//.MYI


			Then use the ‘force’ flag:
			myisamchk --recover --extend-check --force /var/lib/mysql//.MYI


			If those are not MyISAM tables try Mysqlcheck:

			mysqlcheck --all-databases -r #repair databases
			mysqlcheck --all-databases -a #analyze databases
			mysqlcheck --all-databases -o #optimize databases


			to repair one database:
			mysqlcheck -ro database_name


			to repair one table in database:
			mysqlcheck -ro database_name table_name

2012-05-15T01:36:37Z

Crear base de dades MySQL (Actualitzat: Ja no cal, es pot fer posant root account a la web de configuracio):

← Versió més antiga		Revisió del 01:36, 15 maig 2012
Línia 25:		Línia 25:
	I aleshores executar les següents commandes mysql:		I aleshores executar les següents commandes mysql:

	~~create database wikidb identified by 'yourpassword';~~
	~~o bé:~~
	create database wikidb;		create database wikidb;



			grant all on wikidb.* to 'wikiuser'@'localhost' identified by 'yourpassword';
			o més especific:
			grant create, select, insert, update, delete, alter, lock tables on wikidb.* to 'wikiuser'@'localhost';
	set password for 'wikiuser'@'localhost'=password('yourpassword');		set password for 'wikiuser'@'localhost'=password('yourpassword');

	~~grant all on wikidb.* to 'wikiuser'@'localhost';~~
	~~o més especific:~~
	~~grant create, select, insert, update, delete, alter, lock tables on wikidb.* to 'wikiuser'@'localhost' identified by 'password';~~

	flush privileges;		flush privileges;
Línia 38:		Línia 39:


			create database wikidb;
			grant create, select, insert, update, delete, alter, lock tables on wikidb.* to 'wikiuser'@'localhost' identified by 'password';
			flush privileges;
			set password for 'wikiuser'@'localhost'=password('yourpassword');
			\q

	== Basics ==		== Basics ==

2012-05-14T16:36:54Z

Crear base de dades MySQL (Actualitzat: Ja no cal, es pot fer posant root account a la web de configuracio):

2012-05-14T16:27:36Z

Crear base de dades MySQL (Actualitzat: Ja no cal, es pot fer posant root account a la web de configuracio):

← Versió més antiga		Revisió del 16:27, 14 maig 2012
Línia 24:		Línia 24:

	I aleshores executar les següents commandes mysql:		I aleshores executar les següents commandes mysql:

			create database wikidb identified by 'yourpassword';
			o bé:
	create database wikidb;		create database wikidb;
			set password for 'wikiuser'@'localhost'=password('yourpassword');

	grant all on wikidb.* to 'wikiuser'@'localhost';		grant all on wikidb.* to 'wikiuser'@'localhost';
	o més especific:		o més especific:
	grant create, select, insert, update, delete, alter, lock tables on wikidb.* to 'wikiuser'@'localhost' identified by 'password';		grant create, select, insert, update, delete, alter, lock tables on wikidb.* to 'wikiuser'@'localhost' identified by 'password';

	flush privileges;		flush privileges;
	~~set password for 'wikiuser'@'localhost'=password('yourpassword');~~
	\q		\q

2012-05-14T16:23:03Z

@@ Línia 9: / Línia 9: @@
-delete from ---- where ----=1;
+ delete from ---- where ----=1;
 if autocommit=false it is needed to do after any modification/delete:
   commit;