Mysql - Versionsgeschichte

F: /* Datenbank&Benutzer löschen */

2021-03-06T12:50:01Z

Datenbank&Benutzer löschen

← Nächstältere Version		Version vom 6. März 2021, 12:50 Uhr
Zeile 30:		Zeile 30:
	SELECT User,Host FROM mysql.user;		SELECT User,Host FROM mysql.user;
	SHOW GRANTS FOR 'bloguser'@'localhost';		SHOW GRANTS FOR 'bloguser'@'localhost';
			</source>

			=== Passwort ändern ===
			<source lang="sql">
			update user 'user'@'localhost' identified by 'password';
	</source>		</source>

F am 6. März 2021 um 12:46 Uhr

2021-03-06T12:46:26Z

← Nächstältere Version		Version vom 6. März 2021, 12:46 Uhr
Zeile 16:		Zeile 16:
	</source>		</source>

	=== Datenbank rechte neu laden		=== Datenbank rechte neu laden ===

	<source lang=sql>		<source lang=sql>

F am 6. März 2021 um 12:46 Uhr

2021-03-06T12:46:11Z

← Nächstältere Version		Version vom 6. März 2021, 12:46 Uhr
Zeile 14:		Zeile 14:
	create user 'USERNAME'@'localhost' identified by 'PASSWORD';		create user 'USERNAME'@'localhost' identified by 'PASSWORD';
	grant all on DATABASE.* to 'USERNAME'@'localhost';		grant all on DATABASE.* to 'USERNAME'@'localhost';
			</source>

			=== Datenbank rechte neu laden

			<source lang=sql>
			FLUSH PRIVILEGES;
	</source>		</source>

F am 12. Oktober 2020 um 14:30 Uhr

2020-10-12T14:30:26Z

← Nächstältere Version		Version vom 12. Oktober 2020, 14:30 Uhr
Zeile 1:		Zeile 1:
	=== mysql absichern ===		=== mysql absichern ===
			<source lang="bash">
	mysql_secure_installation		mysql_secure_installation
			</source>

	* https://adminforge.de/mysql/mysql-root-benutzer-absichern-und-benutzer-anlegen/		* https://adminforge.de/mysql/mysql-root-benutzer-absichern-und-benutzer-anlegen/

F am 12. Oktober 2020 um 14:29 Uhr

2020-10-12T14:29:58Z

← Nächstältere Version		Version vom 12. Oktober 2020, 14:29 Uhr
Zeile 1:		Zeile 1:
			=== mysql absichern ===

			mysql_secure_installation

			* https://adminforge.de/mysql/mysql-root-benutzer-absichern-und-benutzer-anlegen/

	=== Datenbank&Benutzer anlegen ===		=== Datenbank&Benutzer anlegen ===
	<source lang="bash">		<source lang="bash">

F am 1. Juli 2020 um 08:33 Uhr

2020-07-01T08:33:54Z

← Nächstältere Version		Version vom 1. Juli 2020, 08:33 Uhr
Zeile 69:		Zeile 69:
	</source>		</source>
	* http://www.cyberciti.biz/faq/how-do-i-empty-mysql-database/		* http://www.cyberciti.biz/faq/how-do-i-empty-mysql-database/

			= Probleme =

			== InnoDB: Database page corruption on disk or a failed file read of page ==

			<source>
			2020-07-01T08:00:16.517936Z 0 [ERROR] InnoDB: Database page corruption on disk or a failed file read of page [page id: space=2335, page number=2249]. You may have to recover from a backup.
			....
			2020-07-01T08:00:16.561870Z 0 [Note] InnoDB: Uncompressed page, stored checksum in field1 4125275931, calculated checksums for field1: crc32 929992359/440751498, innodb 3527800627, none 3735928559, stored checksum in field2 4125275931, calculated checksums for field2: crc32 929992359/440751498, innodb 735854216, none 3735928559, page LSN 1 691569793, low 4 bytes of LSN at page end 691569793, page number (if stored to page already) 2249, space id (if created with >= MySQL-4.1.1 and stored already) 2335
			InnoDB: Page may be an update undo log page
			InnoDB: Page may be an index page where index id is 6947
			2020-07-01T08:00:16.561882Z 0 [Note] InnoDB: It is also possible that your operating system has corrupted its own file cache and rebooting your computer removes the error. If the corrupt page is an index page. You can also try to fix the corruption by dumping, dropping, and reimporting the corrupt table. You can use CHECK TABLE to scan your table for corruption. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
			2020-07-01T08:00:16.561891Z 0 [ERROR] [FATAL] InnoDB: Aborting because of a corrupt database page in the system tablespace. Or, there was a failure in tagging the tablespace as corrupt.
			2020-07-01 08:00:16 0x7f53a3fff700 InnoDB: Assertion failure in thread 139997210474240 in file ut0ut.cc line 918
			InnoDB: We intentionally generate a memory trap.
			InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
			InnoDB: If you get repeated assertion failures or crashes, even
			InnoDB: immediately after the mysqld startup, there may be
			InnoDB: corruption in the InnoDB tablespace. Please refer to
			InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
			InnoDB: about forcing recovery.
			</source>

			* Lösung
			I. Force InnoDB Recovery
			<source lang=bash>
			cp -a /var/lib/mysql/* /root/mysql_backup/

			# vi /etc/my.cnf
			vim /etc/mysql/mysql.conf.d/mysql.cnf
			[mysqld]
			innodb_force_recovery = 2
			</source>

			* Quelle
			** https://support.plesk.com/hc/en-us/articles/213939865--How-to-fix-InnoDB-corruption-cases-for-the-MySQL-databases-on-Plesk-for-Linux-

F: Die Seite wurde neu angelegt: „=== Datenbank&Benutzer anlegen === mysql -u root -p create database DATABASE; create user 'USERNAME'@'localh…“

2020-07-01T08:28:50Z

Die Seite wurde neu angelegt: „=== Datenbank&Benutzer anlegen === <source lang="bash"> mysql -u root -p </source> <source lang="sql"> create database DATABASE; create user 'USERNAME'@'localh…“

Neue Seite

=== Datenbank&Benutzer anlegen ===
<source lang="bash">
mysql -u root -p
</source>
<source lang="sql">
create database DATABASE;
create user 'USERNAME'@'localhost' identified by 'PASSWORD';
grant all on DATABASE.* to 'USERNAME'@'localhost';
</source>

=== Datenbank&Benutzer anzeigen ===
<source lang="bash">
mysql -u root -p
</source>
<source lang="sql">
show databases;
SELECT User,Host FROM mysql.user;
SHOW GRANTS FOR 'bloguser'@'localhost';
</source>

=== Datenbank&Benutzer löschen ===
<source lang="bash">
mysql -u root -p
</source>
<source lang="sql">
delete database DATABASE;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'bloguser'@'localhost';
DROP USER 'bloguser'@'localhost';
</source>

=== export import ===
<source lang="bash">
# export
mysqldump --single-transaction --default-character-set=utf8 -u _username_ -p _databasename_ > mysql.yyyymmdd.sql
# import
mysql -u _username_ -p _databasename_ < mysql.yyyymmdd.sql
</source>

=== drop all tables per bash ===

<source lang=bash>
#!/bin/bash
MUSER="$1"
MPASS="$2"
MDB="$3"
MHOST="$4"

# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)

if [ $# -ne 4 ]
then
echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name}"
echo "Drops all tables from a MySQL"
exit 1
fi

TABLES=$($MYSQL -u $MUSER -h $MHOST -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )

for t in $TABLES
do
echo "Deleting $t table from $MDB database..."
$MYSQL -u $MUSER -h $MHOST -p$MPASS $MDB -e "drop table \`$t\`"
done

</source>
* http://www.cyberciti.biz/faq/how-do-i-empty-mysql-database/