How to increase memory size for MySQL Server

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

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 “How To Install MySQL on Debian 9 (Stretch) with version selection” »

Share

Large Database Import Progress Using PV

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

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. Continue reading “Fixing phpmyadmin login on MySQL 5.7 and Debian 9” »

Share

Setup Percona on Debian 7

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: Continue reading “Setup Percona on Debian 7” »

Share

Upgrade mysql 5.5 to 5.6 in Ubuntu 14

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
Share

Increase MySQL connections max_connections

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