Category: MySQL

Left Join on mysql

February 24, 2016 The Geek Decoder No Comments MySQL

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;

IS my mysql database table Innodb or Mysiam

February 19, 2016 The Geek Decoder No Comments MySQL 
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 |                |         |
+-----------------------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+

Why Backup your MySql Databases with Cron

February 19, 2016 The Geek Decoder No Comments Administration, MySQL

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

How To Install MySQL – MariaDB on CentOS 7

January 15, 2016 The Geek Decoder No Comments CentOS 7, MySQL

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

Start, Stop and Check Status for MariaDB

December 13, 2015 The Geek Decoder No Comments MySQL

To stop/restart and disable mariadb service use the following commands:
Stop


# sudo systemctl stop mariadb.service

Restart


# sudo systemctl restart mariadb.service

Disable at boot time


# sudo systemctl disable mariadb.service

Check Status


# sudo systemctl is-active mariadb.service

Reset maria DB password on Plesk

October 6, 2015 The Geek Decoder No Comments MySQL, Plesk

Is it mysql or maria?

# service mysqld status
Redirecting to /bin/systemctl status  mysqld.service
mysqld.service
   Loaded: not-found (Reason: No such file or directory)
   Active: inactive (dead)


]# systemctl status mariadb.service
mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled)
   Active: active (running) since Mon 2015-10-05 21:03:57 EST; 3min 30s ago
  Process: 26953 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
  Process: 26922 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
 Main PID: 26952 (mysqld_safe)
   CGroup: /system.slice/mariadb.service
           ├─26952 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
           └─27120 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock

Oct 05 21:03:55 domain.com mysqld_safe[26952]: 151005 21:03:55 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
Oct 05 21:03:55 domain.com mysqld_safe[26952]: 151005 21:03:55 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Oct 05 21:03:57 domain.com systemd[1]: Started MariaDB database server.

Find the existing password

# cat /etc/psa/.psa.shadow
$AES-128-CBC$MnglmyKq3wJbZVgmb4YKTQ==$p/zX+xpnhJ20kIrrZiFsq7VLE3kI+86JwVdPYJtWdEE=[root@69-64-92-195 ~]#

Stop the database

]# systemctl stop mariadb.service

Edit my.cnf and add skip-grant-tables

[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
skip-grant-tables
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Restart mysql

# systemctl restart mariadb.service

Reset the mysql password no

# mysql -uadmin -p`cat /etc/psa/.psa.shadow` psa
mysql> UPDATE mysql.user SET Password=PASSWORD('# mysql -uadmin -p`cat /etc/psa/.psa.shadow` psa
mysql> UPDATE mysql.user SET Password=PASSWORD('$AES-128-CBC......') WHERE User='admin';') WHERE User='admin';

Edit my.cnf and remove skip-grant-tables

[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
# skip-grant-tables
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

Install mtop on centos 6

October 3, 2015 The Geek Decoder No Comments Administration, MySQL

Install RPMForge on CentOS 6

# wget http://packages.sw.be/rpmforge-release/rpmforge-release-0.5.2-2.el6.rf.x86_64.rpm
# rpm -Uvh rpmforge-release-0.5.2-2.el6.rf.x86_64.rpm

Import RPMForge Repository Key in CentOS 6


# wget http://dag.wieers.com/rpm/packages/RPM-GPG-KEY.dag.txt
# rpm --import RPM-GPG-KEY.dag.txt

Install Mtop in CentOS 6

Once you’ve installed and enabled RPMForge repository, let’s install MTOP using following YUM command.


# yum install mtop

To start Mtop program, you need to connect to your MySQL Server, using following command.


# mysql -u root -p

Then you need to create separate user called mysqltop and grant privileges to him under your MySQL server. To do, this just run the following commands in mysql shell.


mysql> grant super, reload, process on *.* to mysqltop;
Query OK, 0 rows affected (0.00 sec)

mysql> grant super, reload, process on *.* to mysqltop@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye

Let’s start the Mtop program.


# mtop

Sample Output:

load average: 0.03, 0.06, 0.08 mysqld 5.5.44-cll-lve up 4 day(s), 19:08 hrs
12 threads: 1 running, 0 cached. Queries/slow: 5/0 Cache Hit: 100.00%
Opened tables: 0  RRN: 332  TLW: 1  SFJ: 0  SMP: 0  QPS: 0

ID       USER     HOST             DB           TIME   COMMAND STATE        INFO
39965    mysqltop localhost                            Query                show full processlist

Monitor Remote MySQL Server using Mtop


# mtop  –host=remotehost –dbuser=username –password=password –seconds=1