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}';

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
 

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 .