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`);

Manually Creating Wordpress Database and login

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;

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