How to drop all tables in mysql database using a single command

Command:-
mysqldump -u[user] -p[password] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[user] -p[password] [DATABASE]

Here test is the database name. I'm going to drop all the tables from this database.
Example:-
mysqldump -uroot -predhat --add-drop-table --no-data test | grep ^DROP | mysql -uroot -predhat test

Queries regarding Mysql Proceduers

To find out all the procedures of a database:-

SHOW PROCEDURE STATUS WHERE Db = 'thirstydb';


To set a new definer:-
mysql> UPDATE `mysql`.`proc` p SET definer = 'thirstydev@%' WHERE definer='thirstylive@localhost' AND db='devthirstydb';
Query OK, 0 rows affected (0.02 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE `mysql`.`proc` p SET definer = 'thirstydev@%' WHERE definer='thirstylive@%' AND db='devthirstydb';
Query OK, 85 rows affected (0.04 sec)
Rows matched: 85  Changed: 85  Warnings: 0

To select all the procdure of a particular db:-
select name FROM mysql.proc WHERE db = 'devthirstydb' AND type = 'PROCEDURE';

select name FROM mysql.proc WHERE db = 'devthirstydb' AND type = 'FUNCTION';


To delete all the procdure of a particular db:-
DELETE FROM mysql.proc WHERE db = 'devthirstydb' AND type = 'PROCEDURE';

DELETE FROM mysql.proc WHERE db = 'devthirstydb' AND type = 'FUNCTION';




Mysql procedures:-

To find out all the procedures:-

mysql> show procedure status;
+--------------+---------------------------------------+-----------+---------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db           | Name                                  | Type      | Definer       | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+--------------+---------------------------------------+-----------+---------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| thirstydb    | advanced_vendor_filtering             | PROCEDURE | thirstylive@% | 2016-08-12 05:13:27 | 2016-08-12 05:13:27 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| thirstydb    | advanced_vendor_filtering_my_bookings | PROCEDURE | thirstylive@% | 2016-08-12 05:22:29 | 2016-08-12 05:22:29 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| thirstydb    | advanced_vendor_filtering_temp        | PROCEDURE | thirstylive@% | 2016-08-08 08:29:11 | 2016-08-08 08:29:11 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| thirstydb    | all_food_categories                   | PROCEDURE | thirstylive@% | 2016-08-08 08:29:20 | 2016-08-08 08:29:20 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| thirstydb    | apikeyValidity                        | PROCEDURE | thirstylive@% | 2016-08-08 08:29:29 | 2016-08-08 08:29:29 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| thirstydb    | apikeyValidity_vendor                 | PROCEDURE | thirstylive@% | 2016-08-08 08:29:41 | 2016-08-08 08:29:41 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| thirstydb    | directory                             | PROCEDURE | thirstylive@% | 2016-08-19 03:00:51 | 2016-08-19 03:00:51 | DEFINER       |         | utf8


To find out a procedure by a certain definer name and dbname:-
mysq> use mysql
mysql> select name from  proc where definer = 'thirstydev@%' AND db='devthirstydb';

+---------------------------------------+
| name                                  |
+---------------------------------------+
| advanced_vendor_filtering             |
| advanced_vendor_filtering_my_bookings |
| advanced_vendor_filtering_temp        |
| all_food_categories                   |
| apikeyValidity                        |
| apikeyValidity_vendor                 |
| base_url                              |
| directory                             |
| facebook_login  


To call a procedure:-
mysql> call search_result(1);
Empty set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)


To view a procedure:-

mysql> show create procedure user_login;
+------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure  | sql_mode | Create Procedure                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | character_set_client | collation_connection | Database Collation |
+------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| user_login |          | CREATE DEFINER=`thirstylive`@`%` PROCEDURE `user_login`(email_in VARCHAR(255), password_in VARCHAR(255),
api_key_in VARCHAR(50), base_url_in TEXT)
BEGIN

        DECLARE email_verified_declared INT(2);
        DECLARE user_id_declared BIGINT;

        SELECT email_verified INTO email_verified_declared FROM user_profile WHERE email = email_in AND `password` = password_in;
        SELECT user_id INTO user_id_declared FROM user_profile WHERE email = email_in AND `password` = password_in;

        IF email_verified_declared = 0 THEN

                SELECT 'unverified_email' AS  col_name;

        ELSEIF user_id_declared IS NULL THEN

                SELECT 'invalid_credentials' AS col_name;

        ELSE

                UPDATE user_profile SET api_key = api_key_in WHERE user_id = user_id_declared;
                CALL user_profile(user_id_declared, base_url_in);

        END IF;


END | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+-------------------

Mysql error-log monitoring using Shell script with mail alert

#!/bin/bash
SERNAME=xyz.kmi.com
SERPUBIP=192.168.72.50
SERVER=`hostname`
WEEKDAY=`date '+%w%H%M'`
DATE_VAR=`date '+%Y_%m_%d'`

Error_log_loc=/var/log
egrep 'Error' $Error_log_loc/mysqld.log |sort -u > $Error_log_loc/mysqld_ALERTLOG.txt
cat $Error_log_loc/mysqld.log >> $Error_log_loc/mysqld_archived.log

cat /dev/null > $Error_log_loc/mysqld.log

if [ -s "$Error_log_loc/mysqld.log" ] ; then
cat $Error_log_loc/mysqld_ALERTLOG.txt | mail -s "URGENT -ERROR in Oracle Alert Log File for $SERNAME ($SERPUBIP) at `date` " ssdas@gmail.com
fi

# Weekly alert log datestamp and compress (Sunday 00:15)

sysdate=`date | awk '{ print $3}'`
if [[ $sysdate -eq 4 || $sysdate -eq 11 || $sysdate -eq 18 || $sysdate -eq 25 ]] ; then
mv $Error_log_loc/mysqld_archived.log $Error_log_loc/mysqld_archived_ALERTLOG_${DATE_VAR}.log
gzip $Error_log_loc/mysqld_archived_ALERTLOG_${DATE_VAR}.log
fi
exit 0

Monitoring percona Error-log using shell script

vi /backups/alertlog_monotor.sh


#!/bin/bash
SERNAME=srv1.xyz.com
SERPUBIP=192.168.72.50
SERVER=`hostname`                      #### Sets the server name for the email
WEEKDAY=`date '+%w%H%M'`               #### Sets the number value of the day of the week
DATE_VAR=`date '+%Y_%m_%d'`

Error_log_loc=/data/mysql
egrep 'Error' $Error_log_loc/$SERNAME.err |sort -u >  $Error_log_loc/$SERNAME_ALERTLOG.txt
cat $Error_log_loc/$SERNAME.err >> $Error_log_loc/$SERNAME_Archived_ALERTLOG.txt

cat /dev/null > $Error_log_loc/$SERNAME.err

if [ -s "$Error_log_loc/$SERNAME_ALERTLOG.txt" ] ; then
cat $Error_log_loc/$SERNAME_ALERTLOG.txt | mail -s "URGENT -ERROR in Alert Log File for $SERNAME ($SERPUBIP) at `date` " ssdas@24x7-itsupport.com
fi

# Weekly alert log datestamp and compress (Sunday 00:15)

if [[ $WEEKDAY -eq 00015 ]]; then
        mv $Error_log_loc/$SERNAME_Archived_ALERTLOG.txt $Error_log_loc/$SERNAME_Archived_ALERTLOG_${DATE_VAR}.log
        gzip $Error_log_loc/$SERNAME_Archived_ALERTLOG_${DATE_VAR}.log
fi

exit 0

:wq (save & exit)

Now Schedule the above script in crontab for every 15mins.

crontab -l

*/15 * * * * /backups/alertlog_monotor.sh

How to migrate mysql users from one server to another?

Step 1: Get a List of MySQL Users from the source server

[root@db01 tmp]# mysql -B -N -uroot -p -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user != 'debian-sys-maint' AND user != 'root' AND user != ''" mysql > /tmp/mysql_all_users.txt
Enter password: redhat
[root@db01 tmp]# cat /tmp/mysql_all_users.txt
'CitLivehappy'@'%'
'brightonlands'@'%'
'brochure'@'%'
'carver'@'%'
'CitLivehappy'@'localhost'
'brightonlands'@'localhost'
'brochure'@'localhost'
'carver'@'localhost'


Step 2:- Obtain a List of User Privileges

Fire the next command in the terminal to get the privileges:
[root@db01 tmp]# cd /tmp/
[root@db01 tmp]# while read line; do mysql -B -N -uroot -predhat -e "SHOW GRANTS FOR $line"; done < mysql_all_users.txt > mysql_all_users_sql.sql

Basically, the above command reads every individual line in mysql_all_users.txt and tries to get privileges for each user and paste it on mysql_all_users_sql.sql file.
This file will contain the SQL queries to obtain users along with their privileges.

[root@db01 tmp]# cat mysql_all_users_sql.sql
GRANT USAGE ON *.* TO 'CitLivehappy'@'%' IDENTIFIED BY PASSWORD '*1983DE33EA67561F7C46D591CC6F15FD7BF1E'
GRANT ALL PRIVILEGES ON `Cit-Live-bewealthynhappy`.* TO 'CitLivehappy'@'%'
GRANT ALL PRIVILEGES ON `cit_cvbflive`.* TO 'CitLivehappy'@'%'
GRANT USAGE ON *.* TO 'brightonlands'@'%' IDENTIFIED BY PASSWORD '*69DA4F2138FA03F66C29ACDCD7FED678FA'
GRANT ALL PRIVILEGES ON `Cit-Live-brightonlandscaping`.* TO 'brightonlands'@'%'
GRANT USAGE ON *.* TO 'brochure'@'%' IDENTIFIED BY PASSWORD '*EABF42F3B310313FD230662388030CA895BEB'
GRANT ALL PRIVILEGES ON `Cit-Sub-e-brochure`.* TO 'brochure'@'%'
GRANT USAGE ON *.* TO 'carver'@'%' IDENTIFIED BY PASSWORD '*682687FC5D596EB5B7CF4002EDC1ADBE3'
GRANT ALL PRIVILEGES ON `Cit-Live-carverwelding-blog`.* TO 'carver'@'%'
GRANT ALL PRIVILEGES ON `Cit-Live-carverwelding`.* TO 'carver'@'%'
GRANT ALL PRIVILEGES ON `Cit-Live-carverengindia-blog`.* TO 'carver'@'%'
GRANT ALL PRIVILEGES ON `Cit-Live-carverengindia`.* TO 'carver'@'%'

We can see from mysql_all_users_sql.sql that all the users information along with the privileges and the script is almost ready.
Only thing the above script missing is a ";" at the end of every sql command.

We will add ";" at the end of each sql command
[root@db01 tmp]# sed -i 's/$/;/' mysql_all_users_sql.sql

[root@db01 tmp]# cat mysql_all_users_sql.sql
GRANT USAGE ON *.* TO 'CitLivehappy'@'%' IDENTIFIED BY PASSWORD '*1983DE33EA6756C9BE6591CC6F15FD7BF1E';
GRANT ALL PRIVILEGES ON `Cit-Live-bewealthynhappy`.* TO 'CitLivehappy'@'%';
GRANT ALL PRIVILEGES ON `cit_cvbflive`.* TO 'CitLivehappy'@'%';
GRANT USAGE ON *.* TO 'brightonlands'@'%' IDENTIFIED BY PASSWORD '*69DA4F2138FA03F66C29ACDCD6E99678FA';
GRANT ALL PRIVILEGES ON `Cit-Live-brightonlandscaping`.* TO 'brightonlands'@'%';
GRANT USAGE ON *.* TO 'brochure'@'%' IDENTIFIED BY PASSWORD '*EABF42F3B310313FD23CA750C895BEB';
GRANT ALL PRIVILEGES ON `Cit-Sub-e-brochure`.* TO 'brochure'@'%';
GRANT USAGE ON *.* TO 'carver'@'%' IDENTIFIED BY PASSWORD '*682687FC5D596EB5B7CF4002EDC1BBE3';
GRANT ALL PRIVILEGES ON `Cit-Live-carverwelding-blog`.* TO 'carver'@'%';
GRANT ALL PRIVILEGES ON `Cit-Live-carverwelding`.* TO 'carver'@'%';
GRANT ALL PRIVILEGES ON `Cit-Live-carverengindia-blog`.* TO 'carver'@'%';
GRANT ALL PRIVILEGES ON `Cit-Live-carverengindia`.* TO 'carver'@'%';

Now add "flush privileges" to reload the grant tables.
[root@server1 tmp]# echo "flush privileges;" >> mysql_all_users_sql.sql

Step 3: Now lets import the sql file into new server.

Transfer this sql file into destination(new) server and fire up the below command.

[root@db02 tmp]# mysql -u root -ppassword < mysql_all_users_sql.sql

How to setup MariaDB (Master-Slave) Replication on CentOS/RHEL 6x & Ubuntu

Setup MariaDB (Master-Slave) Replication on CentOS/RHEL 6x & Ubuntu
===================================================================

Our Testing Environment Consists of the Following Machines (Both are CentOS 6) :

Master: 192.168.72.220 ser1.kmi.com
Slave: 192.168.72.221 ser2.kmi.com

Step: 1. Bind Hosts File (Both Server) :

# vi /etc/hosts

192.168.72.220 ser1.kmi.com ser1
192.168.72.221 ser2.kmi.com ser2

-- Save & Quit (:wq)

Step: 2. Stop Iptables & Disabled Selinux (Both Server) :

For CentOS:

# service iptables stop
# chkconfig iptables off

For Ubuntu:

# service ufw stop

# vi /etc/sysconfig/selinux

SELINUX=disabled

-- Save & Quit (:wq)

Step: 3. Restart the Both Server :

# init 6

Step: 4. Install NTP :

# yum -y install ntp
# service ntpd restart
# chkconfig ntpd on
# ntpdate pool.ntp.org

Step: 5. Create MariaDB.repo on Both Server :

For CentOS:

# rpm --import http://yum.mariadb.org/RPM-GPG-KEY-MariaDB

# vi /etc/yum.repos.d/MariaDB.repo

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

-- Save & Quit (:wq)

For Ubuntu :

# apt-get install software-properties-common
# apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
# add-apt-repository 'deb [arch=amd64,i386] http://mirrors.neusoft.edu.cn/mariadb/repo/10.1/ubuntu trusty main'

Step: 6. Install MariaDB on Both Servers :

For CentOS:

# yum -y install MariaDB-server MariaDB-client

For Ubuntu:

# apt-get update
# apt-get install mariadb-server

Step: 7. Start MariaDB Service & Set MariaDB Root Password on Both Master & Slave Server :

# service mysqld start
# chkconfig mysqld on

# mysql_secure_installation

Step: 8. Check MariaDB Version on Both Node :

# mysql -V
mysql  Ver 15.1 Distrib 10.1.10-MariaDB, for Linux (x86_64) using readline 5.1

Step: 9. Configuring MariaDB Server on Master Node :

# mv /etc/my.cnf /etc/my.bak
# vi /etc/my.cnf

[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock

[mysqld]
server_id=1
replicate-do-db=employees
bind-address=0.0.0.0
log-bin=mysql-bin
binlog_format=mixed
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

-- Save & Quit (:wq)

Step: 10. Restart the MariaDB Service :

# service mysqld restart

Step: 11. Log on to MariaDB Master Node & Create a User Called "Slave" & Give Appropriate Permission :

# mysql -u root -proot_password

MySQL [(none)]> STOP SLAVE;
MySQL [(none)]> GRANT REPLICATION SLAVE ON *.* TO slave IDENTIFIED BY 'SlavePassword' WITH GRANT OPTION;
MySQL [(none)]> FLUSH PRIVILEGES;
MySQL [(none)]> FLUSH TABLES WITH READ LOCK;
MySQL [(none)]> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      181 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> exit

Step: 12. Now Take Backup of Full Database :

# mysqldump -u root -proot_password employees > /tmp/employees.sql

Step: 13. After Taken Dump, Log on to MariaDB & Unlock Tables :

# mysql -u root -proot_password

MySQL [(none)]> UNLOCK TABLES;
MySQL [(none)]> exit;

Step: 14. Copy the Dump File to the Slave Node :

# scp -r /tmp/employees.sql root@192.168.72.221:/tmp/

Step: 15. Restore the Dump File on Slave Node :

# mysql -u root -predhat

MySQL [(none)]> create database employees;
MySQL [(none)]> exit;

# mysql -u root -proot_password employees < /tmp/employees.sql

Step: 16. Configuring MariaDB on Slave Node :

Note: It needs to be a Different Integer than 1, as we used 1 in the Master:

# mv /etc/my.cnf /etc/my.bak
# vi /etc/my.cnf

[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock

[mysqld]
server_id=2
replicate-do-db=employees
bind-address=0.0.0.0
log-bin=mysql-bin
binlog_format=mixed
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

-- Save & Quit (:wq)

Step: 17. Restart the MariaDB Server :

# service mysqld restart

Step: 18. Setting Up MariaDB Replication on Slave Node :

Note: Special attention to the "MASTER_LOG_FILE" & "MASTER_LOG_POS" Variables, which should match the Values returned by "SHOW MASTER STATUS" in STEP 10 of above.

# mysql -u root -proot_password

MySQL [(none)]> STOP SLAVE;
MySQL [(none)]> CHANGE MASTER TO
  MASTER_HOST='192.168.72.220',
  MASTER_USER='slave',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=181,
  MASTER_CONNECT_RETRY=10;
 
Step: 19. Start the Slave & Check its Status :

MySQL [(none)]> START SLAVE;
MySQL [(none)]> SHOW SLAVE STATUS\G;

Note: If the SHOW SLAVE STATUS\G; command returns any Errors. Use those errors to troubleshoot & then run START SLAVE; to test again.

Step: 20. Test MariaDB Database Replication :

Add some Record to the "employees" Database in the Master Node :

MySQL [(none)]> use employees;
MySQL [(none)]> CREATE TABLE Guests1 (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
);

Verify that this Change was Replicated in the Slave Node :

MySQL [(none)]> USE employees;
MySQL [(none)]> show tables;

As you can see, Replication is Working Correctly from Master to Slave.

Done...!!!

Mysql up & down status check script with email alert

create the shell-script which will monitor the up/down status of mysql service:-

[oracle@server1 ~]$ vi /home/script/alert.sh

#!/bin/bash
servername="server1.example.com"
publicip=192.168.72.5
servicename="mysql"

for i in $servicename
do
/sbin/service $i status
if [ $? -ne 0 ]; then
/sbin/service $i status | mailx -s "Check Status of $i in $servername ($publicip) at `date`"  yourmailid@gmail.com
fi
done

:wq (save & exit)

We schedule the script on crontab which will check in every 5 mins.
[oracle@server1 ~]$ crontab -e
*/5 * * * * /home/script/alert.sh > /dev/null

P.S.Make sure mailx rpm is installed and any  mailing service such as sendmail/postfix is running on the server.
To  install sendmail:-
#yum -y install sendmail

To install mailx:-
#yum -y install mailx

How to Install Mysql 5.7 on Centos 6.X/7.X

Step 1: Save the Repo File for The MySql 5.7 or Install the RPM for Mysql 5.7

[root@server1 ~]# vi /etc/yum.repos.d/mysql-community.repo

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

--- save n quit (:wq) ---

or,

For RHEL/CentOS 7
[root@server1 ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm
[root@server1 ~]# yum localinstall mysql57-community-release-el7-7.noarch.rpm

For RHEL/CentOS 6
[root@server1 ~]# wget http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm
[root@server1 ~]# yum localinstall mysql57-community-release-el6-7.noarch.rpm

Step 2: Install Mysql 5.7

[root@server1 ~]# yum install mysql-community-server -y


Step 3. Start mysql.
[root@server1 ~]# service mysqld start
Starting mysqld:  [  OK  ]

Step 4: Reset the Mysql Temporary root Password
[root@server1 ~]# cat /var/log/mysqld.log |grep "temporary password"
2015-12-20T15:11:54.175060Z 1 [Note] A temporary password is generated for root@localhost: eZWxCsme5V+q

# mysql_secure_installation

Enter password for user root: <Enter the Temporary Password>

The existing password for the user account root has expired. Please set a new password.

New password: <New Password>

Re-enter new password: <Retype the Password>
****************
p.s. If you get an error like this
"Re-enter new password:
 ... Failed! Error: Your password does not satisfy the current policy requirements"

Add the following parameter in my.cnf file under [mysqld] section.
[root@server1 ~]# vi /etc/my.cnf
validate_password_policy=LOW

:wq

The error is caused by default password policy of mysql. Here we changed its policy to low to bypass the error.
****************
Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

Step 4: Check the Version and Mysql Once

# mysql -u root -p<password>

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> \q
Bye

[root@server1 ~]# mysql --version
mysql  Ver 14.14 Distrib 5.7.10, for Linux (x86_64) using  EditLine wrapper

------ DONE, Thanks --------

How to find out a particular tablesize in mysql

mysql> SELECT
    ->     CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',SUBSTR(units,pw1*2+1,2)) DATSIZE,
    ->     CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',SUBSTR(units,pw2*2+1,2)) NDXSIZE,
    ->     CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',SUBSTR(units,pw3*2+1,2)) TBLSIZE
    -> FROM
    -> (
    ->     SELECT DAT,NDX,TBL,IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    ->     FROM
    ->     (
    ->         SELECT data_length DAT,index_length NDX,data_length+index_length TBL,
    ->         FLOOR(LOG(IF(data_length=0,1,data_length))/LOG(1024)) px,
    ->         FLOOR(LOG(IF(index_length=0,1,index_length))/LOG(1024)) py,
    ->         FLOOR(LOG(data_length+index_length)/LOG(1024)) pz
    ->         FROM information_schema.tables
    ->         WHERE table_schema='cyclexaftest'
    ->         AND table_name='log_url'
    ->     ) AA
    -> ) A,(SELECT 'B KBMBGBTB' units) B;
+---------+---------+----------+
| DATSIZE | NDXSIZE | TBLSIZE  |
+---------+---------+----------+
| 5.52 MB | 6.03 MB | 11.55 MB |
+---------+---------+----------+
1 row in set (0.00 sec)


-----------------------------------------------------
Another query:-

mysql> SELECT table_name AS `Table`,
    -> round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
    -> FROM information_schema.TABLES
    -> WHERE table_schema = "cyclexaftest"
    ->  AND table_name = "log_url";
+---------+------------+
| Table   | Size in MB |
+---------+------------+
| log_url |      11.55 |
+---------+------------+
1 row in set (0.00 sec)

How To find out largest 10 tables in Mysql database

mysql> SELECT CONCAT(table_schema, '.', table_name),
    ->        CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
    ->        CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
    ->        CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
    ->        CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
    ->        ROUND(index_length / data_length, 2)                                           idxfrac
    -> FROM   information_schema.TABLES
    -> ORDER  BY data_length + index_length DESC
    -> LIMIT  10;


+-------------------------------------------+-------+-------+-------+------------+---------+
| CONCAT(table_schema, '.', table_name)     | rows  | DATA  | idx   | total_size | idxfrac |
+-------------------------------------------+-------+-------+-------+------------+---------+
| test.sg_postmeta                     | 0.38M | 0.03G | 0.02G | 0.05G      |    0.62 |
| primary_prod.log_url_info                | 0.34M | 0.04G | 0.00G | 0.04G      |    0.00 |
| primary_prod.log_visitor_info            | 0.24M | 0.04G | 0.00G | 0.04G      |    0.00 |
| primary_prod.log_url                     | 0.33M | 0.01G | 0.01G | 0.03G      |    0.97 |
| primary_prod.log_visitor                 | 0.26M | 0.02G | 0.00G | 0.02G      |    0.00 |
| test.sg_posts                       | 0.05M | 0.01G | 0.01G | 0.01G      |    0.81 |
| primary_prod.report_event                | 0.05M | 0.00G | 0.01G | 0.01G      |    3.01 |
| primary_prod.report_viewed_product_index | 0.03M | 0.00G | 0.01G | 0.01G      |    2.88 |
| crm.vtiger_profile2field                  | 0.03M | 0.00G | 0.00G | 0.01G      |    3.00 |
| crm.vtiger_role2picklist                  | 0.01M | 0.00G | 0.00G | 0.00G      |    0.57 |
+-------------------------------------------+-------+-------+-------+------------+---------+
10 rows in set (1.07 sec)

How to reset Mysql Root password

How to reset Mysql Root password:-

[root@server1 ~]# mysql -u root -predhat

To change root password.
mysql> update user set Password=PASSWORD('newpassword') WHERE User='root';
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4  Changed: 4  Warnings: 0

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

If root password is not available or its lost but we need to reset it, follow this.

[root@server1 ~]# service mysql stop
Shutting down MySQL..[  OK  ]
[root@server1 ~]# mysqld_safe --skip-grant-tables &
[1] 7670
[root@server1 ~]# 140222 06:06:03 mysqld_safe Logging to '/var/lib/mysql/server1.soumya.com.err'.
140222 06:06:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql


Now the server is running with the –skip-grant-tables flag you can connect to it without a password and reset root password:

[root@server1 ~]# mysql --user=root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> update user set Password=PASSWORD('newpassword') WHERE User='root';
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4  Changed: 4  Warnings: 0

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

mysql> exit
Bye

Now restart the mysql service and we are good to go..
[root@server1 ~]# service mysql stop
Shutting down MySQL..[  OK  ]
[root@server1 ~]# service mysql start

[root@server1 ~]# mysql -u root -pnewpassword
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

How to change default character set to UTF-8 in mysql 5.6

Step 1:- If mysql is already running stop it.

[root@server1 ~]# service mysql stop
Shutting down MySQL..[  OK  ]

Step 2:- Add the following lines in my.cnf file and start mysql.

[root@server1 ~]# vi /etc/my.cnf
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8


--save & exit (:wq)

[root@server1 ~]# service mysql start

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.02 sec)


mysql> show variables like 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

Specify Character Settings per Database:
mysql> create database soumya
    -> DEFAULT CHARACTER SET utf8
    -> DEFAULT COLLATE utf8_general_ci;
Query OK, 1 row affected (0.06 sec)

Tables created in the database will use utf8 and utf8_general_ci by default for any character columns.

--Done

Shell script for Mysql Database Backup

#  mkdir /backups/db_backup/

# vi /backups/mysqlbackup.sh 

#!/bin/bash
export path1=/backups/db_backup
date1=`date +%y%m%d_%H%M%S`
/usr/bin/find /backups/db_backup/* -type d -mtime +30 -exec rm -r {} \; 2> /dev/null
cd $path1/
mkdir $date1
USER="root"
PASSWORD="redhat123"
OUTPUTDIR="$path1/$date1"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
HOST="localhost"
databases=`$MYSQL --user=$USER --password=$PASSWORD --host=$HOST \
-e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
echo "` for db in $databases; do
   echo $db

       if [ "$db" = "performance_schema" ] ; then
       $MYSQLDUMP --force --opt --single-transaction --lock-tables=false --skip-events --user=$USER --password=$PASSWORD --host=$HOST --routines \
        --databases $db | gzip > "$OUTPUTDIR/$db.gz"
        else

$MYSQLDUMP --force --opt --single-transaction --lock-tables=false --events --user=$USER --password=$PASSWORD --host=$HOST --routines \
   --databases $db | gzip > "$OUTPUTDIR/$db.gz"
fi
done `"

:wq



Now schedule the script inside crontab:-
#The  script will run every night at 12 A.M
#crontab -e
0 0 * * * /backups/mysqlbackup.sh > /dev/null


Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

User creation in Mysql where dbname consist hyphen

We can create a user that consists of no special character at ease.

mysql>  create user 'abc'@'localhost' identified by 'redhat';

But to create a user for a database which consists of special character we follow this:-

mysql> grant all privileges on `Emp-Kolkata`.* to soumya@'localhost' identified by 'redhat';

mysql> grant all privileges on `Emp-Kolkata`.* to soumya@'%' identified by 'redhat';

mysql> flush privileges;

So the above command shows how to create a user for a dbname that consists hyphen in it and we granted all privileges to that user for both localhost and remote location.




Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

Mysql Database backup & restore command

Mysql single database backup:-

mysqldump --force --opt --single-transaction --lock-tables=false --skip-events --user=root --password=xxxx --routines --databases testdb > singledb.sql

Mysql all database backup:-

mysqldump --force --opt --single-transaction --lock-tables=false --skip-events --user=root --password=xxxx --routines --all-databases > all_databases.sql

Mysql Restore database:

mysql -u root -predhat dbname < /mnt/dbname.sql



Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

RPM Packages for MYSQL 5.6 for RHEL 6

RPM Packages of MYSQL 5.6 for RHEL 6

Download link:-

MySQL-shared-compat-5.6.16-1.el6.x86_64.rpm :- https://onedrive.live.com/redir?resid=B9CA6221A841D988!223&authkey=!AMa8AftKb_lqNpw&ithint=file%2crpm

MySQL-embedded-5.6.16-1.el6.x86_64.rpm :- https://onedrive.live.com/redir?resid=B9CA6221A841D988!224&authkey=!ADwJirI81CEkNII&ithint=file%2crpm


MySQL-client-5.6.16-1.el6.x86_64.rpm :- https://onedrive.live.com/redir?resid=B9CA6221A841D988!229&authkey=!AH5t4UWp6D1NPMk&ithint=file%2crpm

MySQL-devel-5.6.16-1.el6.x86_64.rpm:- https://onedrive.live.com/redir?resid=B9CA6221A841D988!227&authkey=!AI8_nSebOGg-s8I&ithint=file%2crpm

MySQL-server-5.6.16-1.el6.x86_64.rpm:- https://onedrive.live.com/redir?resid=B9CA6221A841D988!228&authkey=!AMFWXuGrmYtBh_4&ithint=file%2crpm

MySQL-shared-5.6.16-1.el6.x86_64.rpm:- https://onedrive.live.com/redir?resid=B9CA6221A841D988!225&authkey=!AJT7lgzWWH_Zl-I&ithint=file%2crpm

MySQL-test-5.6.16-1.el6.x86_64.rpm:- https://onedrive.live.com/redir?resid=B9CA6221A841D988!226&authkey=!AHtBP6ksXCtblUQ&ithint=file%2crpm



Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

Percona XtraDB Cluster configuration on CentOS 6.4

Percona XtraDB Cluster configuration on CentOS 6.4

Host 1: 192.168.72.22
Host 2: 192.168.72.23
Linux version : Centos 6.4
Percona version : 5.5

Prerequisites:-

All the nodes must have a CentOS 6.4 installation.
Firewall Must be disabled or atleast 3306 port must be open.
SELinux must be disabled.
Check if Mysql is already installed in the server also check if its running.
Remove mysql if its present by doing
$yum erase mysql
This has to be done in all nodes where mysql is present.Else this will conflict with the installation files of Percona.

Step 1:-(On both nodes)
Create Percona yum Repository

$ rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

Step 2:-(On both nodes)
Install XtraDB Cluster

$ yum install Percona-XtraDB-Cluster-server-55 Percona-XtraDB-Cluster-client-55 Percona-XtraDB-Cluster-galera-2

Step 3:-(On both nodes)
Percona yum Experimental repository

$ rpm -Uhv http://repo.percona.com/testing/centos/6/os/noarch/percona-testing-0.0-1.noarch.rpm


Step 4:-
Configuring the node1

Create a file in the following location

vim /etc/my.cnf  and enter the following lines

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.72.22,192.168.72.23

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This is a recommended tuning variable for performance
innodb_locks_unsafe_for_binlog=1

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #1 address
wsrep_node_address=192.168.72.22

# SST method
wsrep_sst_method=xtrabackup

# Cluster name
wsrep_cluster_name=my_centos_cluster

# Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"
wsrep_replicate_myisam=1
~
~
--save & exit (:wq)

After saving the file run the following command
/etc/init.d/mysql start --wsrep-cluster-address="gcomm://"

Now to enter in mysql:-
mysql -u root
Now update mysql root password:-
UPDATE mysql.user SET password=PASSWORD("r00t123007") where user='root';

mysqladmin -u root password NEWPASSWORD
Step 5:-Check the cluster status in node1

$mysql -u root -predhat
mysql> show status like 'wsrep%';
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid     | c2883338-834d-11e2-0800-03c9c68e41ec |
...
| wsrep_local_state          | 4                                    |
| wsrep_local_state_comment  | Synced                               |
...
| wsrep_cluster_size         | 1                                    |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
...
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+
40 rows in set (0.01 sec)


This output shows that the cluster has been successfully bootstrapped.

In order to perform successful State Snapshot Transfer using XtraBackup new user needs to be set up with proper privileges:

mysql@percona1> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cr3t123#';
mysql@percona1> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
mysql@percona1> FLUSH PRIVILEGES;

Note MySQL root account can also be used for setting up the SST with Percona XtraBackup, but it’s recommended to use a different (non-root) user for this.

Step 6:-
Configuring node2

Create a file in the following location

vim /etc/my.cnf  and enter the following lines

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.72.22,192.168.72.23

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This is a recommended tuning variable for performance
innodb_locks_unsafe_for_binlog=1

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #2 address
wsrep_node_address=192.168.72.23

# Cluster name
wsrep_cluster_name=my_centos_cluster

# SST method
wsrep_sst_method=xtrabackup

#Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"
wsrep_replicate_myisam=1


After saving the file run the following command
[root@percona2 ~]# /etc/init.d/mysql start


Step 7:-Checking the cluster status on node2
$mysql -u root -predhat
mysql> show status like 'wsrep%';
mysql> show status like 'wsrep%';
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid     | c2883338-834d-11e2-0800-03c9c68e41ec |
...
| wsrep_local_state          | 4                                    |
| wsrep_local_state_comment  | Synced                               |
...
| wsrep_cluster_size         | 2                                    |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
...
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+
40 rows in set (0.01 sec)


Step 8:-
Testing the replication

mysql@percona2> CREATE DATABASE percona;
Query OK, 1 row affected (0.01 sec)

Creating the example table on the second node:
mysql@percona3> USE percona;
Database changed

mysql@percona3> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));
Query OK, 0 rows affected (0.05 sec)

Inserting records on the first node:
mysql@percona1> INSERT INTO percona.example VALUES (1, 'percona1');
Query OK, 1 row affected (0.02 sec)

Retrieving all the rows from that table on the second node:
mysql@percona2> SELECT * FROM percona.example;
+---------+-----------+
| node_id | node_name |
+---------+-----------+
|       1 | percona1  |
+---------+-----------+
1 row in set (0.00 sec)






Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.