MySQL

Delete/Purging mysql-bin (binlog) files safely

by

The file mysql-bin.index keeps a list of all binary logs mysqld has generated and auto-rotated. The mechanisms for cleaning out the binlogs in conjunction with mysql-bin.index are:

PURGE BINARY LOGS TO ‘binlogname’;
PURGE BINARY LOGS BEFORE ‘datetimestamp’;

These will clear all binary logs before the binlog or timestamp you just specified. For example, if you login to mysql run

mysql> PURGE BINARY LOGS TO ‘mysql-bin.000223’;

this will erase all binary logs before ‘mysql-bin.000223’.

If you run

mysql> PURGE BINARY LOGS BEFORE DATE(NOW() – INTERVAL 3 DAY) + INTERVAL 0 SECOND;

this will erase all binary logs before midnight 3 days ago.

If you want to have binlog rotated away automatically and keep 3 days woth, simply set this:

mysql> SET GLOBAL expire_logs_days = 3;

then add this to /etc/my.cnf

[mysqld]
expire-logs-days=3

Share

Installing MySQL on Debian

by

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.

Read more

Share

How to increase memory size for MySQL Server

by

To increase the memory size for a MySQL Server, follow these steps:

1.Enter management mode by typing your password and pressing Enter twice. Select Exit to terminal using the arrow keys and then press Enter

2.Type:

nano /etc/my.cnf

3.Locate the line innodb_buffer_pool_size = 1024M and change the number to 50% of RAM of the VM. 1024M means 1024 megabytes.

4.Press Ctrl+X to exit the text editor, then press Y to save.

5.Reboot the appliance using the Restart system option in management mode.

Share

How To Install MySQL on Debian 9 (Stretch) with version selection

by

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.

Read more

Share

Large Database Import Progress Using PV

by

PV can report progress for large data imports. In this example I will use a large (7 G) install on Ubuntu. First, download PV (http://linux.die.net/man/1/pv)

sudo apt-get install pv

A traditional mysql import may be formatted as:

mysql -u USER -p DATABASE_NAME < DUMP.mysql

but to leverage PV, pass the dump file ,then pipe the mysql command specifying the database with -D flag:

pv DUMP.mysql | mysql -u USER -p -D DATABASE_NAME

Your import will report progress and an ETA 🙂

Share

Fixing phpmyadmin login on MySQL 5.7 and Debian 9

by

Once setting up the LAMP stack, you must be wondering to see that you no longer been able to login phpmyadmin using root credentials.

MySQL 5.7 changed the secure model: now MySQL root login requires a sudo (while the password still can be blank). I.e., phpMyAdmin will be not able to use root credentials.

The simplest (and safest) solution will be create a new user and grant required privileges.

1. Connect to mysql

sudo mysql --user=root mysql

2. Create a user for phpMyAdmin
Run the following commands (replacing some_pass by the desired password):

CREATE USER 'phpmyadmin'@'localhost' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

If your phpMyAdmin is connecting to localhost, this should be enough.

Read more

Share

Setup Percona on Debian 7

by

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:

Read more

Share