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