MySQL | Bots! - Page 2

Setup Percona on Debian 7

November 23, 2017 by Rumi

MySQL multi-master replication is an excellent feature within MySQL. However, there is only one problem; standard multi-master replication seems to never be as stable as something like master-slave replication. It is always in need of attention. That is where Percona comes into play. The Percona team has developed an amazing product dubbed Percona XtraDB cluster. XtraDB features world class multi-master replication powered by Galera. So, what are we waiting for? Let’s get started.

Prerequisites
A Linux distro of your choice. In this guide, we will be using Debian 7. You can use a different distro if you would like. (Note that you may need to adapt this guide to work with the distro of your choice)
Two nodes running the same OS. Basic knowledge of the command line and SSH.

Getting Started

SSH into your virtual machines.

VM 1:
ssh root@xxx.xxx.xxx.xxx
VM 2:
ssh root@yyy.yyy.yyy.yyy

Add Percona’s repositories.
On both nodes, execute the following command:

echo -e "deb http://repo.percona.com/apt wheezy main\ndeb-src http://repo.percona.com/apt wheezy main" >> /etc/apt/sources.list.d/percona.list && apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

Now we need to update the sources:

apt-get update

Install Percona-XtraDB Cluster

The installation is straightforward:

Moving MySQL/Windows to same version of MySQL/Linux

August 5, 2017 by Rumi

Moving MySQL/Windows to same version of MySQL/Linux. You can mysqldump all the databases as follows:

C:\> mysqldump -uroot -p --routines --triggers --flush-privileges --all-databases > MySQLData.sql

Move MySQLData.sql to Linux box and run the reload

mysql -uroot -p < MySQLData.sql

Upgrade mysql 5.5 to 5.6 in Ubuntu 14

June 2, 2017 by Rumi

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

Increase MySQL connections max_connections

January 19, 2017 by Rumi

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

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

January 17, 2017 by Rumi

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.

Reset a MySQL root password for Debian

July 14, 2016 by Rumi

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.

Reinstalling MySQL on CentOS/Redhat 6

February 7, 2016 by Rumi

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

Importing Big mysqldump with Progress Bar PV

June 6, 2014 by Rumi

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

How to reset MySQL Root Password in Linux