Installing MySQL on Debian

Step 1 – Prerequisites

Login to your Debian 9 system using shell access. For remote systems connect with SSH. Windows users can use Putty or other alternatives applications for SSH connection.

ssh root@debian9

Run below commands to upgrade the current packages to the latest version.

sudo apt update 
sudo apt upgrade

Step 2 – Configure MySQL PPA

MySQL team provides official MySQL PPA for Debian Linux. You can download and install the package on your Debian system, which will add PPA file to your system. Run below command to enable PPA.

wget http://repo.mysql.com/mysql-apt-config_0.8.13-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.13-1_all.deb

During the installation of MySQL apt config package, It will prompt to select MySQL version to install. Select the MySQL 5.7 or 5.6 option to install on your system. Continue reading “Installing MySQL on Debian” »

Share

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