Database Tips

Some common tricks for quick database optimizations
See what is slowing things down -
`show full processlist;

Creating Index in old databases
Sometimes adding index will give error when moving from 5.7 to 8 to change that you can set the sql_mode
SELECT @@SESSION.sql_mode; // See what is the current mode
// For ex for Date error 
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
ALTER TABLE `wp_posts` ADD INDEX `post_date_gmt` (`post_date_gmt`);

Upgradeing to InnoDB
Quick upgrade to InnoDB
SET @DATABASE_NAME = 'name_of_your_db';

SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM information_schema.tables AS tb
WHERE table_schema = @DATABASE_NAME
AND `ENGINE` = 'MyISAM'
AND `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

You will get the queries for all the databases , execute them to convert the tables. If an error regarding the data then set sql_mode . That should resolve the issue .
Manually Creating Wordpress Database and login
To list the users -
SELECT User, Host FROM mysql.user;

log into mysql and -
 CREATE DATABASE d_{name};
 CREATE USER u_{name}@localhost IDENTIFIED BY '{password}';
 GRANT ALL ON d_{name}.* TO u_{name}@locahost; 
 flush privileges;
 

sometimes grant will require password ( older versions )
GRANT ALL ON d_{name}.* TO u_{name}@locahost identifed by '{password}';

GRANT ALL PRIVILEGES ON d_{name}.* TO u_{name}@locahost WITH GRANT OPTION;

Manual setting up wordpress database
 wp core install --title="Site Title" --url={domain_name} --admin_user=siteadmin --admin_password={password} --admin_email={email} --skip-email --allow-root

Flush Tables issue
Run command from /srv/www
find -maxdepth 3 -name "wp-config.php" -exec cat {} \; | grep DB_USER

Search replace using -
(u_[A-Z0-9]+)
GRANT RELOAD ON *.* TO '\1'@'localhost';

Run it inside mysql and it should work now . GRANT reload can only be global.
Importing databases
Import Single Database
mysql -uroot -D d_J2MK3T -o d_J2MK3T < db_dump.sql

Intsall mssql client
https://docs.microsoft.com/en-us/sql/connect/php/installation-tutorial-linux-mac?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/connect/php/microsoft-php-drivers-for-sql-server-support-matrix?view=sql-server-ver15#supported-operating-systems
Get the base ready
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
apt-get update 
apt-get install mssql-tools unixodbc-dev

Now lets install PHP modules
apt install php-pear
apt install php7.4-dev
pecl install sqlsrv
pecl install pdo_sqlsrv
# Create the file sqlsrv.ini and pdo_sqlsrv.ini in modsavailbile 

# Create links in config 

ln -s /etc/php/7.4/mods-available/pdo_sqlsrv.ini 20-pdo_sqlsrv.ini
ln -s /etc/php/7.4/mods-available/sqlsrv.ini 20-sqlsrv.ini

SSL Bug : https://github.com/microsoft/msphpsql/issues/1112#issuecomment-609972220
Database Recovery
This is to be done if the innodb mysql crashes. First try to do things without putting the DB in recovery mode. If it does not work move to steps below.
Stop Mysql , to copy its files
service stop mysql
cp -rf /var/lib/mysql /var/lib/mysql.orig

Add innodb_force_recovery = 1 in my.cnf under [mysqld] , you can increase value from 1 - 6 where mysql starts to work.
Dump all database names -
mkdir ~/recovery/
mysql -e 'show databases;' | grep -v information_schema | grep -v Database > ~/recovery/database_list.txt

Next dump all databases -
for db in `cat /mnt/temp_storage/database_list.txt`; do mysqldump --skip-lock-tables $db > /mnt/temp_storage/backup/$db.sql;done

Now we drop the databases -
for db in `cat /mnt/temp_storage/database_list.txt`; do mysqladmin drop $db ; done

Incase Database tale does not drop , please drop it manually , also move teh ibdata files
cd /var/lib/mysql
rm -rf database_name
mv /var/lib/mysql/ibdata1 /mnt/temp_storage/backup/
mv /var/lib/mysql/ib_logfile0 /mnt/temp_storage/backup/
mv /var/lib/mysql/ib_logfile1 /mnt/temp_storage/backup/
 

Remove innodb_force_recovery = 1 from my.cnf
Start mysql and create the datrabases
for db in `cat /mnt/temp_storage/database_list.txt`; do mysqladmin create $db ; done

Import all the databases
for db in `cat /mnt/temp_storage/database_list.txt`; do mysqldump $db < /mnt/temp_storage/backup/$db.sql;done

Repair Databases if any
mysqlcheck --repair --all-databases

The above should fix the issue hopefully .
Database Recovery 2
This menthod is the last resort , Incase the datable is very large and the mysql dump keeps failing. We will manually delete the databases.
innodb_force_recovery = 6
This is readonly mode mainly for dumping and not command which mae changes will work.
Step 1 is to run mysql check and see which databases it is faling.
service mysql stop
mysqlcheck -c -u root -p --all-databases

Mae a note of all databases which have errors , no move those databases out of the /var/lib/mysql directory .
mv d_AHDKIT ~/db_backup
mkdir d_AHDKIT 
chown -R mysql:mysql d_AHDKIT

Now try starting mysql after removing innodb_force_recovery
service mysql start 

Now drop the database with the errors.
DROP DATABASE d_AHDKIT;

Now you can recreate the database , grant priviliges to use and import from backup.