Tag Archives: MySQL

Increase MySQL connections max_connections

by

0

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)’

by

0

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

by

0

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

by

0

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

by

0

 

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

by

0

 

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

by

0

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

by

0

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

MySQL Root Password Reset

by

0

First things first. Log in as root and stop the mysql daemon. Now lets start up the mysql daemon and skip the grant tables which store the passwords.

mysqld_safe –skip-grant-tables

You should see mysqld start up successfully. If not, well you have bigger issues. Now you should be able to connect to mysql without a password.

mysql –user=root mysql
update user set Password=PASSWORD('new-password') where user='root';
flush privileges;
exit;

Now kill your running mysqld, then restart it normally. You should be good to go. Try not to forget your password again.
 

Share

MySQL Master-Master or Master-Slave Replication Error Fix

by

Scenario Master – Master replication

MasterA is a client facing server
MasterB is a warm standby server (read only)

MasterB restarted abruptly and when instances were braught back up MasterA (it’s slave) was showing the following error:

MasterA has the following error in show slave status:

Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’

Solution:

Slave: stop slave;

Master: flush logs

Master: show master status; — take note of the master log file and master log position

Slave: CHANGE MASTER TO MASTER_LOG_FILE=’log-bin.00000X′, MASTER_LOG_POS=106;

Slave: start slave;

Read more: http://mysqlpreacher.com/wordpress/2010/12/could-not-find-first-log-file-name-in-binary-log-index-file/#ixzz2ILRnl4XI

Src: http://mysqlpreacher.com/wordpress/2010/12/could-not-find-first-log-file-name-in-binary-log-index-file/

Share