Mysql

mysql absichern

mysql_secure_installation

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

Datenbank&Benutzer anlegen

mysql -u root -p

create database DATABASE;
create user 'USERNAME'@'localhost' identified by 'PASSWORD';
grant all on DATABASE.* to 'USERNAME'@'localhost';

Datenbank rechte neu laden

FLUSH PRIVILEGES;

Datenbank&Benutzer anzeigen

mysql -u root -p

show databases;
SELECT User,Host FROM mysql.user;
SHOW GRANTS FOR 'bloguser'@'localhost';

Passwort ändern

update user 'user'@'localhost' identified by 'password';

Datenbank&Benutzer löschen

mysql -u root -p

delete database DATABASE;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'bloguser'@'localhost';
DROP USER 'bloguser'@'localhost';

export import

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

drop all tables per 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

• 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

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.

• Lösung

I. Force InnoDB Recovery

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

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