UPDATE wp_options SET option_value = replace(option_value, ‘http://www.oldurl’, ‘http://www.newurl’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’;

UPDATE wp_posts SET guid = replace(guid, ‘http://www.oldurl’,’http://www.newurl’);

UPDATE wp_posts SET post_content = replace(post_content, ‘http://www.oldurl’, ‘http://www.newurl’);

UPDATE wp_postmeta SET meta_value = replace(meta_value,’http://www.oldurl’,’http://www.newurl’);

mysql> UPDATE wp_options SET option_value = replace(option_value, ‘http://www.oldurl’, ‘http://www.newurl’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0

mysql> UPDATE wp_posts SET guid = replace(guid, ‘http://www.oldurl’,’http://www.newurl’);
Query OK, 0 rows affected (0.02 sec)
Rows matched: 964 Changed: 0 Warnings: 0

mysql> UPDATE wp_posts SET post_content = replace(post_content, ‘http://www.oldurl’, ‘http://www.newurl’);
Query OK, 0 rows affected (0.05 sec)
Rows matched: 964 Changed: 0 Warnings: 0

mysql> UPDATE wp_postmeta SET meta_value = replace(meta_value,’http://www.oldurl’,’http://www.newurl’);g
Query OK, 0 rows affected (0.01 sec)
Rows matched: 686 Changed: 0 Warnings: 0

# mysql -u root -p 
MariaDB [(none)]> wp_database -e "EXPLAIN SELECT * FROM posts"
 EXPLAIN SELECT * FROM 2qGr3sjm_posts;
+------+-------------+----------------+------+---------------+------+---------+------+------+-------+
| id   | select_type | table          | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+-------------+----------------+------+---------------+------+---------+------+------+-------+
|    1 | SIMPLE      |          posts | ALL  | NULL          | NULL | NULL    | NULL | 2519 |       |
+------+-------------+----------------+------+---------------+------+---------+------+------+-------+

Or from command line

#  mysql -u root -p wp_database -e "EXPLAIN SELECT * FROM posts"

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

or:


SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

PS! In some databases LEFT JOIN is called LEFT OUTER JOIN.

Example: You have a surveys table and a services table and the id’s are both client_id


select surveys.date, surveys.client_id, surveys.score 
from surveys 
left join services on surveys.client_id=services.client_id 
where cpu like Xeon
group by surveys.client_id;

MariaDB [(none)]> show table status from admin_geek01;
+-----------------------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name                        | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation       | Checksum | Create_options | Comment |
+-----------------------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| 2qGr3sjm_commentmeta        | MyISAM |      10 | Dynamic    |    0 |              0 |        2704 |  281474976710655 |         9216 |      2704 |           1073 | 2015-12-10 13:59:26 | 2015-12-30 22:51:44 | 2016-01-29 23:01:12 | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_comments           | MyISAM |      10 | Dynamic    |    0 |              0 |        8888 |  281474976710655 |         7168 |      8888 |            530 | 2015-12-10 13:59:26 | 2015-12-30 22:51:44 | 2016-01-29 12:35:32 | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_links              | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         1024 |         0 |              1 | 2015-12-10 13:59:26 | 2015-12-10 13:59:26 | 2015-12-10 13:59:26 | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_options            | InnoDB |      10 | Compact    |  167 |           6671 |     1114112 |                0 |        16384 |   6291456 |          18191 | 2015-12-10 13:59:26 | NULL                | NULL                | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_postmeta           | MyISAM |      10 | Dynamic    | 1758 |            106 |      187856 |  281474976710655 |       116736 |         0 |           7564 | 2015-12-10 13:59:26 | 2016-02-19 11:42:42 | 2016-02-07 11:40:30 | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_posts              | InnoDB |      10 | Compact    | 3547 |           1926 |     6832128 |                0 |       442368 |   6291456 |           2966 | 2015-12-10 13:59:26 | NULL                | NULL                | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_stb_styles         | MyISAM |      10 | Dynamic    |    7 |            845 |        5920 |  281474976710655 |         8192 |         0 |           NULL | 2015-12-10 13:59:26 | 2015-12-10 13:59:26 | NULL                | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_term_relationships | MyISAM |      10 | Fixed      |  899 |             21 |       18900 | 5910974510923775 |        46080 |        21 |           NULL | 2015-12-10 13:59:26 | 2016-02-19 11:36:09 | 2016-02-07 11:40:30 | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_term_taxonomy      | MyISAM |      10 | Dynamic    |  105 |             39 |        4148 |  281474976710655 |         9216 |         0 |            108 | 2015-12-10 13:59:26 | 2016-02-19 11:36:09 | 2016-02-07 11:40:29 | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_termmeta           | InnoDB |      10 | Compact    |    0 |              0 |       16384 |                0 |        32768 |   6291456 |              1 | 2015-12-10 13:59:26 | NULL                | NULL                | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_terms              | MyISAM |      10 | Dynamic    |  105 |             30 |        3176 |  281474976710655 |        13312 |         0 |            108 | 2015-12-10 13:59:26 | 2015-12-10 13:59:26 | 2015-12-10 13:59:26 | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_usermeta           | InnoDB |      10 | Compact    |   37 |            442 |       16384 |                0 |        32768 |   6291456 |             41 | 2015-12-10 13:59:26 | NULL                | NULL                | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_users              | MyISAM |      10 | Dynamic    |    1 |            120 |         120 |  281474976710655 |         4096 |         0 |              2 | 2015-12-10 13:59:26 | 2015-12-10 13:59:26 | 2015-12-10 13:59:26 | utf8_general_ci |     NULL |                |         |
+-----------------------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+

There are a few reasons to install a cron job to create full database dumps versus copying the /var/lib/mysql data. The MySQL service may have a lock on the database file(s) when it attempts to replicate the data file, causing it to fail; if the backup on the data files does succeed, the InnoDB log files may not match with the ibdata increasing the chance of full corruption or lost data; MyISAM data and table structures can face similar issues.

Plesk

Create a location for the dumps. This will be the folder the dumps are exported to by the script.

# mkdir -pv /usr/local/db/dumps

Create a script that cron can execute. Create /usr/local/bin/dbdump with these contents:

nano /usr/local/bin/dbdump

Put this in the file (for Plesk)

#!/bin/bash
for db in $(mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e 'show databases' -s --skip-column-names); do echo -n "dumping $db..."; mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` $db > "/usr/local/db/dumps/$db.sql"; echo "[done]"; done

Make it executable:

# chmod +x /usr/local/bin/dbdump

For non plesk:

# nano /usr/local/bin/dbdump

Put this in the file

for db in $(mysql -uroot -pPASSWORD -e 'show databases' -s --skip-column-names); do echo -n "dumping $db..."; mysqldump -uroot -pPASSWORD $db > "/usr/local/db/dumps/$db.sql"; echo "[done]"; done

Make it executable:

# chmod +x /usr/local/bin/dbdump

Create /etc/cron.d/dbdump with these contents:

# nano /etc/cron.d/dbdump
# Cronjob to dump databases nightly
05     0      *       *       *       root       /usr/local/bin/dbdump >/dev/null 2>&1

Install MySQL / MariaDB
Installing MariaDB is as simple as running just one command:


# yum -y install mariadb-server mariadb

And then start MySQL, now MariaDB:


# systemctl start mariadb

Be sure that MySQL/MariaDB starts at boot:


# systemctl enable mariadb

To check the status of MySQL/MariaDB:


# systemctl status mariadb

To top MySQL/MariaDB:


# systemctl stop mariadb

Check the installation with the command client:


# mysql