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.