Puppet : Automate/Reset first time root password for MySQL 5.7.x

Hello,

This article will help you reset first time root password for MySQL 5.7.x with Puppet.

I was looking for some custom puppet code/module to automate first root password for MySQL 5.7.x, I didn’t get proper puppet code to get through so I have decided to try and run my own code with puppet.

Puppet with MySQL 5.7
Puppet Labs

Pre-requisites :-

1. MySQL 5.7.x version should be installed before running this manifest/module.

Following are the steps to create Puppet module to reset first time password for MySQL 5.7.x

1. Make Puppet Module directory mysql

mkdir /etc/puppetlabs/code/environments/production/modules/

2. Create manifests directory.

mkdir /etc/puppetlabs/code/environments/production/modules/mysqlserver/manifests

3. Now create init.pp and put following code to the file.

class mysql57pwreset(
 $mysql_password = 'MySQL@99#') #Set password of your choice within single quotes
{
 
service { "mysqld":
 enable => true,
 ensure => running,
 }

$mysqlpwstring = "MYPW=$(grep 'temporary password' /var/log/mysqld.log | awk '{print \$NF}' | tail -n1) && mysql -uroot -p\${MYPW} --connect-expired-password -e \"ALTER USER 'root'@'localhost' IDENTIFIED BY '$mysql_password' PASSWORD EXPIRE NEVER;\""

File { "/tmp/mysql-pw.sh":
 content => $mysqlpwstring,
 mode => "0700",
 owner => "root",
 group => "root",
}


exec { "set-mysql-pw":
 path => [ '/bin', '/usr/bin', '/usr/local/bin', '/usr/sbin' ],
 unless => "mysqladmin -uroot -p$mysql_password status",
 require => Service["mysqld"],
 command => "sh /tmp/mysql-pw.sh",
 logoutput => on_failure,
}

}

4. Include mysql57pwreset module to /etc/puppetlabs/code/environments/production/manifests/site.pp

5. That’s it. Now you can execute puppet agent -t on puppet agent node, It will reset MySQL 5.7.x password.

Like us on Facebook : https://www.facebook.com/AllLinuxUsersBlog

HowTo: Install MySQL Workbench on RHEL 5.x / CentOS 5.x | x86_64

Hello

What is MySQL Workbench ?

MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more. MySQL Workbench is available on Windows, Linux and Mac OS X.

MySQL Workbench
MySQL Workbench

Sharing article which will help you all to Install MySQL Workbench on RHEL 5.4 / CentOS 5.4 x86_64 (64-Bit).

Please follow below steps to install MySQL Workbench on RHEL 5.x / CentOS 6.x 64 Bit version.

1. You need to be root to install RPMs.

2. Download MySQL-Workbench RPM from below link,

[root@tejasbarot ~]# wget ftp://ftp.pbone.net/mirror/dev.mysql.com/pub/Downloads/MySQLGUITools/mysql-workbench-oss-5.2.17-1centos.el5.x86_64.rpm

3. Download dependencies from below link:

[root@tejasbarot ~]#  wget http://tejasbarot.com/RPMs/downloads/mysql-workbench.tar.gz
[root@tejasbarot ~]# tar zxvf mysql-workbench.tar.gz
[root@tejasbarot ~]# cd mysql-workbench
[root@tejasbarot mysql-workbench]# rpm -Uvh *.rpm

4. Now Next step to Install downloaded RPM MySQL-Workbench in step 2.

[root@tejasbarot ~]# yum -y localinstall mysql-workbench-oss-5.2.17-1centos.el5.x86_64.rpm --nogpgcheck

5. That’s it. Now Wait for dependencies to be installed and MySQL Workbench will be ready for you.

Hope this will be helpful to you all.

If you like this then Please Click Google +1 Button and Show Your Support. Your Support will encourage me to write more articles.

Please Keep in Touch with Social Networking :- 

Facebook Page :- https://www.facebook.com/AllLinuxUsersBlog

MySQL: Drop all tables from Database using Script / Linux

Hello,

Few days back, I got a task to do, Task was Keep the database as it is, So that we do not have to add database users, privileges and everything again and again, Just Drop all the databases tables inside the database. If there are only few tables like 5 or 10 than it is easy to do it manually, but what if you have the 100s or 1000s of tables inside database ?

I was in same situation, there were more than 500 tables and  I need to drop it quickly as it was production server, So for the same I managed to do it easily by using few command line parameters and loop, After that I have created script for the same which helps me to drop those tables very easily, You don’t have to worry about each and every table, It will keep database as it is but will drop all the tables from the selected database.

I am sharing that script and steps how to use that script, Would request you to test on non production environment first, Wherever you are executing this script its totally on your risk.

MySQL Script
MySQL Script

 

SUGGESTION: Take a Backup of your database / tables / MySQL Before executing Script, So if anything goes wrong you can also recover.

Steps to execute script :-

1. Download Script

tejas-barot@linux-ahmedabad:~$ wget tejasbarot.com/Scripts/dropall_mysql_tables.sh

2. Give executable permissions

tejas-barot@linux-ahmedabad:~$ chmod +x dropall_mysql_tables.sh

3. Now Let’s execute Script

tejas-barot@linux-ahmedabad:~$ ./dropall_mysql_tables.sh

4. Provide name of the MySQL database from which you want to drop all the tables.

Enter Database name:                         <—- Provide Database name here
Enter MySQL root Password:          
<—- Provide root password of MySQL Here

5. Script will take a Full database backup into /tmp, but I would request don’t depend on this, before executing script take backup.

6. That’s it wait for few minutes, It will drop all the tables from MySQL Database.

Enjoy MySQL 🙂 Enjoy Linux 🙂 Enjoy Open Source 🙂

Please Keep in Touch with Social Networking :- 
Facebook Page :- https://www.facebook.com/AllLinuxUsersBlog

Reset / Recover MySQL root Password / MySQL / Linux

Hello,

Perform this Howto to Reset / Recover root password of MySQL.

There are many cases that we used to forget root password of MySQL, So Perform just few steps and you will able to reset root mysql password.

MySQL
MySQL

Perform below steps to Recover / Reset MySQL Password.

1. Stop MySQL Service

[root@linux-support-ahmedabad-tejas-barot ~]# /etc/init.d/mysqld stop

2. Execute Following Command

[root@linux-support-ahmedabad-tejas-barot ~]# mysqld_safe --skip-grant-tables and Press ENTER

3. Now Let’s Login to MySQL Without Password

 

[root@linux-support-ahmedabad-tejas-barot ~]# mysql -u root

4. Use Following Queries to Reset/Change your MySQL root Password

mysql> use mysql;

mysql> update user set password=PASSWORD(“NEW-MYSQL-ROOT-PASSWORD”) where User=’root’;
mysql> flush privileges;
mysql> quit

5. Let’s Stop MySQL Server as we have started in Step 2

[root@linux-support-ahmedabad-tejas-barot ~]# service mysqld stop

6. Now Start MySQL Service for using Databases

[root@linux-support-ahmedabad-tejas-barot ~]# service mysqld start

7. Execute Following Command to Login with New Password 

[root@linux-support-ahmedabad-tejas-barot ~]# mysql -u root -p

Hope this will helps you all, If you face any issue regarding the same or its not working for you some how then please raise your questions / issues at http://linuxforums.tejasbarot.com

If you like this then Please Click Google +1 Button and Show Your Support. Your Support will encourage me to write more articles.

All Linux User’s Blog Mobile Applications :- http://www.tejasbarot.com/download-mobile-apps/

Please Keep in Touch with Social Networking :- 

Facebook Page :- https://www.facebook.com/AllLinuxUsersBlog

Enjoy MySQL :-) Enjoy Linux :-) Enjoy Open Source :-) 

Change root password of MySQL

To Change root Password of mysql you can change it by using

mysqladmin command.

if you want to change or update root password then execute this command

mysqladmin -u root -p’oldpassword’ password newpassword

For example, If old password is tejas, and set new password to tejasbarot, enter:

mysqladmin -u root -p’tejas’ password tejasbarot

if you want to change password of other user of MySQL then execute this command

mysqladmin -u <your-username> -p oldpassword newpass

Enjoy 🙂

Installing,Starting Service & Setting root password of My Sql

First set Yum repository of MySQL

then install it by using following command

yum -y install mysql-server mysql 
or
yum -y install mysql* 

Starting MySQL Server:-

Execute Following Command:-
chkconfig mysqld on
/etc/init.d/mysqld start or service mysqld restart

Setting Up root Password:-
mysqladmin -u root password NEWPASSWORD

Testing MySQL Connectivity 

mysql -u root -p.
Enjoy 🙂