Install Mydumper Myloader on Centos 7

If you want to execute logical backups using an alternative to mysqldump that works with parallel threads giving faster execution times mydumper is the correct tool.

wget https://github.com/maxbube/mydumper/releases/download/v0.9.5/mydumper-0.9.5-2.el7.x86_64.rpm

In order to use mydumper and myloader you can use the following sample commands:

For dumping/exporting database:

mydumper -u <username> -p <password> -B <database name> -d /path/to/dump

For importing database (for percona or master-master replication):

myloader -u <username> -p <password> -B <database name> -d /dump/path --enable-binlog

Continue reading “Install Mydumper Myloader on Centos 7” »

Share

Exporting Percona Database

Exporting percona database is however a bit tricky way to make funcitonal. The usual-

mysqldump -u root -p [database] > database.sql

will stuck and shoot you many error. All you need is the add an extended parameters to execute the dump, and here it is:

mysqldump -u root -p [database] lock-for-backup > database.sql

And that should do the trick!

Src: 

https://www.percona.com/doc/percona-server/5.7/flexibility/extended_mysqldump.html

Share

Install Percona XtraDB Cluster for MySQL 5.7 on Debian 8

First of all, why we choose three nodes and not only two? In any cluster, the number of nodes should be odd, so in the case of disconnection of a node, we assume that the highest group of servers has the fresh data, and should be replicated to the down node to avoid data loss. This is related only to resolve conflicts in data replication, we won’t loose data written only to the disconnected node.

This is used to avoid a circumstance called split brain, in which we can’t automatically choose which node has correct data. Think for example of a 2 node cluster where both nodes are disconnected from each other, and the same record is written to both nodes: who wins when they come back online? We don’t know, so split brain happens, and we have to manually decide wich record is the right one.

The number of nodes that is needed to determine wich part of the cluster has the right data is called QUORUM, in our case, the quorum will be 2. So we need 2 servers always be connected to each other. In case all three nodes will go down, we have a split brain and we must decide wich server should go in bootstrap mode manually, this is the procedure to determine wich will be the main server to resume from the split brain.

Configuring Percona XtraDB Cluster on Debian 8

This tutorial describes how to install and configure three Percona XtraDB Cluster nodes on Debian 8 servers, we will be using the packages from the Percona repositories.

Node 1
Hostname: mysql1.local.vm
IP address: 192.168.152.100
Node 2
Hostname: mysql2.local.vm
IP address: 192.168.152.110
Node 3
Hostname: mysql3.local.vm
IP address: 192.168.152.120

On each host, modify file /etc/hosts as follows to ensure DNS will work correctly. Continue reading “Install Percona XtraDB Cluster for MySQL 5.7 on Debian 8” »

Share

MySQL my.cnf tweak for faster importing large database

Few small hack will ease you importing large sized mysql dump in a bit faster process. The trick worked for me for a 70 GB dumped sql, can’t guarantee it’d work for you!

Step-1

need to change the following:

innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0

Why these settings ? Continue reading “MySQL my.cnf tweak for faster importing large database” »

Share

Perfect way to reset MySQL Root Password on Debian/Ubuntu

If you forgot your MySQL root password, you can reset it by following these steps.

1. Stop the MySQL service.

service mysql stop

2. Start MySQL without password and permission checks.

mysqld_safe --skip-grant-tables &

3. Press [ENTER] again if your output is halted.

4. Connect to MySQL.

mysql -u root mysql

5. Run following commands to set a new password for root user. Substitute NEW_PASSWORD with your new password.

UPDATE user SET password=PASSWORD('NEW_PASSWORD') WHERE user='root';
FLUSH PRIVILEGES;

6. Restart the MySQL service.

service mysql restart
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

MySQL Master-Master Replication

This article is about setting up MySQL Master-Master database replication between two Cloud Servers. Master-Master data replication allows for replicated data, stored on multiple computers, to be updated by any authorized contributing member of the group. This allows for more open collaboration than Master-Slave replication where any needed changes identified by a group member must to be submitted to the designated “master” of the node.

The operating system we will use is Debian 5 (Lenny).

Setup Outline

  • We will have two Cloud Servers, named debian501 and debian502 for the purpose of this exercise. Both servers have two IP addresses (one public, one private). We will configure the replication to be done over the private IP interface so that we don’t incur any bandwidth charges.

Installing MySQL

  • First we need to install MySQL on both the Debian Cloud Servers. As always, prior to installing any packages, we need to make sure that our package list is up to date and our locale/language settings are configured properly.
  • Update the package database:
 #aptitude update
  • Install locales (optional):
 #aptitude install locales #dpkg-reconfigure locales
  • The dpkg-reconfigure locales command will bring up a locales setting window where you can choose the locales for your system depending on your country and region. In this case we have chosen en_GB.UTF-8.
  • Now, you can run the following commands to install MySQL:
 #aptitude install mysql-server mysql-client libmysqlclient15-dev

Continue reading “MySQL Master-Master Replication” »

Share