Upgrade mysql 5.5 to 5.6 in Ubuntu 14

While I was installing idoit- the cmdb and IT documenting platform, struggling with a pre-requisite of having mysql version 5.6 which is not shipped by native in ubuntu 14 installations. So had to google it and found some solution, however the one that worked for me which I’m sharing below:

step 1 : remove old mysql

sudo apt-get remove mysql-server
sudo apt-get autoremove

step 2 : install new version of mysql

sudo apt-get install mysql-client-5.6 mysql-client-core-5.6
sudo apt-get install mysql-server-5.6
Share

Increase MySQL connections max_connections

If you need to increase MySQL Connections without MySQL restart do like below

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 100 |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL max_connections = 150;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 150 |
+-----------------+-------+
1 row in set (0.00 sec)
These settings will change at MySQL Restart.

For permanent changes add below line in my.cnf and restart MySQL

max_connections = 150
Share

Fixing error: ‘Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES)’

For all you Ubuntu/MySQL developers out there, have you ever seen the following?

neo@thematrix:~$ sudo /etc/init.d/mysql restart
* Stopping MySQL database server mysqld [fail]
* Starting MySQL database server mysqld [ OK ]
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'

So, what is this “debian-sys-maint” user?  Well, this MySQL user is created for the Ubuntu to be able to start/stop the database and to carry out other maintenance operations.

Sounds well enough, but then why do I keep running into the “access denied” problem for this user?  Well, the issue is that with each update to MySQL, the user’s password in the database is overwritten.  Ubuntu seems to go to the file /etc/mysql/debian.cnf in order to find this user’s password, but obviously the password is out of sync after the update has been applied.

As a result of this behaviour, I’ll run into the “access denied” problem every so often.  Thankfully, the solution to this issue is fairly simple. Continue reading “Fixing error: ‘Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES)’” »

Share

Reset a MySQL root password for Debian

Use the following steps to reset a MySQL root password by using the command line interface.

Stop the MySQL service
(Ubuntu and Debian) Run the following command:

sudo /etc/init.d/mysql stop

(CentOS, Fedora, and Red Hat Enterprise Linux) Run the following command:

sudo /etc/init.d/mysqld stop

Start MySQL without a password
Run the following command. The ampersand (&) at the end of the command is required. Continue reading “Reset a MySQL root password for Debian” »

Share

Reinstalling MySQL on CentOS/Redhat 6

Some time we faces issues with MySQL installation on Linux machine. If we simply remove MySQL packages and re-install doesn’t fixes the issue, in that case old settings may still exists on server which again affects new install. In that case first uninstall MySQL completely from system and erase all settings of old install. To do the same follow the below settings.

Note: Please do not use below steps if MySQL have any running databases.

Step 1: Uninstall MySQL Packages
First uninstall all the MySQL packages installed on your server

# yum remove mysql mysql-server

Step 2: Romove MySQL Directory
Now we need to remove MySQL data directory from system which by default exists at/var/lib/mysql. If you didn’t find this, It may be changed to some other place, which you can find in my.cnf file with variable datadir. Delete the /var/lib/mysql directory from system but we prefer to rename it to keep a backup of existing files.

# mv /var/lib/mysql /var/lib/mysql_old_backup Continue reading “Reinstalling MySQL on CentOS/Redhat 6” »

Share

Importing Big mysqldump with Progress Bar PV

 

I am using CentOS 6.4 box, so it requires me to install EPEL repo at the first place:
$ rpm -Uhv http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
Install pv using yum:
$ yum install -y pv
Installation done. Let the importing begin!
$ pv /home/user/my_big_dump.sql | mysql -uroot -p
For Ubuntu/Debian distribution intall PV using-
apt-get install pv
 
Share

How to reset MySQL Root Password in Linux

 

First, Let’s stop the mysql service, In Red Hat’s family (Red Hat Enterprise, Fedora, CentOS) the Mysql Daemon is called ‘mysqld‘, but in Debianlike Ubuntu based distributions and OpenSuse its called ‘mysql‘. I’ll be using the debian based naming
sudo service mysql stop
OR
sudo /etc/init.d/mysql stop
Now, we’ll start the server in safe mode and skip grant tables so that it wont ask for the password we had lost when we try to login Continue reading “How to reset MySQL Root Password in Linux” »
Share

Importing Big mysqldump with Progress Bar PV

I am using CentOS 6.4 box, so it requires me to install EPEL repo at the first place:

$ rpm -Uhv http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

Install pv using yum:

$ yum install -y pv

Installation done. Let the importing begin!

$ pv /home/user/my_big_dump.sql | mysql -uroot -p

For Ubuntu/Debian distribution intall PV using-

apt-get install pv

 

Share

MySQLDump Shell script for backup

You can use the following script to dump mysql database and can add to crontab entry for daily backup!

#!/bin/sh

DAY=`/bin/date +%Y%m%d`
TFILE="/backup/MYDB.$DAY.gz"
mysqldump -u root -p'MYPASSWORD' MYDB | gzip > $TFILE
echo "cache dump completed, dump script by rumi (hasan.emdad@mango.com.bd)"

Share