MYSQL: Default Basic Table Structure WordPress

If you use WordPress and have tested some plugins and removed them later, it is possible that you have much waste tables still inside your database.

  • First do Database Backup with MySQL DUMP
  • Then check it enter the mysql console as your mysql admin user and do:

$mysql -u mysqladminusername -p

mysql > use databasename;

mysql > show tables;

System echo:

+---------------------------+
| Tables_in_wordpressdatab  |
+---------------------------+
| wp_commentmeta            |
| wp_comments               |
| wp_links                  |
| wp_options                |
| wp_postmeta               |
| wp_posts                  |
| wp_term_relationships     |
| wp_term_taxonomy          |
| wp_termmeta               |
| wp_terms                  |
| wp_usermeta               |
| wp_users                  |
+---------------------------+
12 rows in set (0.00 sec)

If this default structure is seen the plugins tables are removed correctly.

If not do, delete waste tables with:

mysql > drop wp_old_plugin_table_name;

logout with „quit“ and test the WordPress Blog with logins and handling.

Remark: You never know what changes plugins inside your database do, the authors are unknown by yourself! Less is more secure!

More Details of WordPress Tables Basics Details

WordPress: Change Word on all content sites with mysql

If you run a WordPress Blog or other CMS with MySQL Backend you can easy change one same word or expression on all content sites on the fly

  • Do a mysqldump at first of the current database

$ mysqldump -u username -p dbname > dbbackup.sql

  • Login to the MySQL server Console
  • Switch to the Database

mysql> use dbname;

  • run command for tests, lists touched posts

mysql> SELECT * FROM wp_posts WHERE (post_content LIKE '%expression%');

  • Run Replace code to pre, cause i use preformat text plugin for the copy & paste boxes

mysql> UPDATE wp_posts SET post_content = REPLACE ( post_content, 'code', 'pre');

  • exit
  • test results..

WordPress: Monitor the Size of the MYSQL Database

If you use WordPress or a other Blog Software with a MySQL Database over years it is useful to do some things regularly:

  • Check the Database Size weekly, that no Skript Kid has found a Backdoor and fills up the Database silent
  • Purge Database Caches with default maintenance tools of the Software
  • Dont forget to create a MySQL Dump Backup weekly with cron

To Check the Size login on mysql command prompt do:

$mysql -u dbuser -p #Enter Password
mysql > use dbwordpress
mysql > SELECT table_schema "Data Base Name", sum( data_length + index_length) / 1024 / 1024 
"Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema;

System Echo a Table of all of your Databases with size!

If the size is much more bigger than last week and you haven’t changed anything you can try to cleanup the Database by for example with a upgrade.php, update.php or other commandline PHP Scripts of the PHP Kit (WordPress, Drupal..) Maintenance Tools. Often many little things are cached into the MySQL Databases. If this doesn’t help, rewind to older MySQL Dump and test again. Don’t forget to keep the old WordPress-DB and rewinded WordPress-DB MySQL Dump secure! If the Database grows again fast, login to WordPress, check comments area of the posts sites. If comments are allowed by Guests, disable them to prevent Spammers.

WordPress: Replace or Purge Text on all Posts by mysql

If you have to remove a link, text or else on all wordpress posts pages with same signs like the „read more“ tag do:

 mysql -u dbadmin -p 

– enter password
– change database by:

use databasename

– run change command(SET „more-tag-source-html-pre“ WordPress reformat the output here! ):

mysql > UPDATE wp_posts SET post_content = REPLACE (post_content,'<!!--more-->',''); 

– System Echo’s:

Query OK, 88 rows affected (0.01 sec)
Rows matched: 706 Changed: 88 Warnings: 0

WordPress: Move Blog to new URL Domain HTTP HTTPS on the MYSQL Console

Info: Today most Search Engines prefer Blogs who are offering „HTTPS“ at the URL for secure direct connects to the Websites but this results a lot of headaches to the Website Developers.

To be effective you should know:

  • You have to offer HTTPS beside HTTP
  • You should use a validated Domain Certificate, most Domain Resellers offer them for FREE
  • You should know that NOT every FREE Certificate is TRUSTED by every Browser (green closed lock symbol at the adressbar)
  • You have to Develop Websites WITH variable Format output ( PC, Tablet, Smartphone, Smart-TV, Infoboxes,)
  • Design Websites and test 3:2, 4:3, 16:9, 21:9, Formats
  • Easy readable for handicapted People
  • Easy to print to PDF (Test Print Preview Page)
  • Test with different Browsers ( IE, Firefox, Midori, Chrome, Safari, Mobile Browsers like Webview, Silk) if you have no possibilities ask Friends to test

To move the Blog you must access the MySQL Server and the Blog Database! phpMyAdmin Admin Interface should NOT be used if you can use the Terminal for higher Security! If not access the phpMyAdmin Admin Weblogin only be Local IP!!!

MySQL Server:

  • Always remove Anonymous User!
  • Always replace the root User Account with a new one
  • Always use very long Database Passwords! minimum 12 digits !
  • Set if you can the Listen IP to localhost or Socket for the MYSQL Server, no one should be able to access the server via public IP. If you have a virtual Server without second IP install a Firewall like „ufw“ and block all Ports without 25, 80, 443!!

Database Changes:

  • Login to the Server via SSH and connect to the MySQL Server with $mysql -u databaseadmin -p
  • Enter following MySQL Commands:

 

mysql > use blogdatabasename;  #change to the blog database
mysql > UPDATE wp_posts SET guid = replace(guid, 'http://www.domain','https://www.domain');
mysql > UPDATE wp_posts SET post_content = replace(post_content, 'http://www.domain', 'https://www.domain');
mysql > UPDATE wp_options SET option_value = replace(option_value, 'http://www.domain', 'https://domain');
mysql > UPDATE wp_posts SET guid = replace(guid, 'http://www.domain','https://www.domain');
mysql > UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.domain','https://www.domain');
mysql > flush query cache; #force updates on the mysql server caches!
mysql > quit;

Testing:

  • Login into your Blog on WordPress and check results and view, save and change all basics settings again on area settings an reading, cause much files are often „cached“ by Browsers, Proxy’s or by Cache Plugins!
  • Change wp-config.php add this „define(‚FORCE_SSL_ADMIN‘, true);“ to force https for Admin Login.
  • Check the Widgets! Cause you can’t change all URL’s at the MySQL Database, cause the Widgets creates „dynamic“ URL-Path Entry’s!!!

PHPMYADMIN: Performance Monitor not working Java Script Error

If you install phpmyadmin on your Server Version 4.XX, the Browser shows a Javascript Error if the Performance Monitor is opened. Problem is the mostly the setting „localhost“ on the config.inc.php file.

If you want to view the monitor from external, set the ip to 192.XXX.XXX.XXX or domainname and control that the MYSQL Server is listening on all IPs!

Warning! If you dont really need this  tool DONT CHANGE the MYSQL Server IP to listening „ALL“ cause LOCALHOST is much more secure! Localhost should work if you have installed a local proxy on the Server! So you can relay the Interface!

phpmyadmin performance javascript error

MySQL : Backup and Restore Database without phpmyadmin (commandline)

Problem: phpmyadmin interfaces are slow and often attacked by script kids, if you can don’t use it!

Solution: To backup and restore use command line

To Backup a database:

 
$mysqldump -u root -p  wordpressdb > /backups/wordpressdb-dump.sql [Enter root password]

To restore:

$mysql -u root -p wordpressdb < /backups/wordpressdb-dump.sql[Enter root password]

Remark: this is not recogized by the ONLINE SYSTEM if users visits you pages to read! There must be no shutdown of the mysql or apache webserver, its a online HOT-Dump. If you like put it into a script and set cron to backup every night.
Sample Bash MySQL Script for full automatic dumps:
#!/bin/bash
## automatic dump database and add time and date stamp ##
date=`date +%d%m%Y-%H%M`                       # set current date value
mysqldump wordpressdb > /backups/wordpressdb-$date.sql  # save+date
exit  # close script after work

To save all Databases on one MySQL-Server change
mysqldump --all-databases > /backups/server-all-$date.sql

now run
#crontab -e

set time to run:
@daily   sh /scriptpath/backup-databases.sh > /dev/null 2>&1

This runs daily backup at 00:00AM and post no message to Admin (root) if you need mails remove (>…1)

WordPress MySQL: Cleanup old revisions old posts old entrys

Problem: If you use wordpress and edit often your posts a high count of old revisions are backuped inside the mysql database:

Solution:
Login mysql

$mysql -u root -p [Enter root password]

at mysql command line select wordpress your database
mysql>use wordpressdb; [Enter]
System echo=Database changed..

delete old backups of posts
mysql>DELETE FROM wp_posts WHERE post_type = "revision"; [Enter]
System echo=..Entry affected time..

logout database and revisit pages, if you have made a database backup with mysqldump before and after cleanup you see how much size the database lost (du -h wordpress.sql)
often 80% of the size.