realtime view mysql queries

Install mytop package

apt-get install mytop

View realtime queries in localhost

mytop -h 127.0.0.1 -d mysql -u root -s 3 --prompt
Advertisements

phpmyadmin with multiple servers

how to configure phpmyadmin with two servers

vi /path/to/phpmyadmin/config.inc.php
/* Servers configuration */
$i = 0;

/* Server: 192.168.0.7 */
$i++;
$cfg['Servers'][$i]['verbose'] = '';
$cfg['Servers'][$i]['host'] = '192.168.0.7';
$cfg['Servers'][$i]['port'] = '';
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['user'] = '';
$cfg['Servers'][$i]['password'] = '';

/* Server: 192.168.0.8 */
$i++;
$cfg['Servers'][$i]['verbose'] = '';
$cfg['Servers'][$i]['host'] = '192.168.0.8';
$cfg['Servers'][$i]['port'] = '';
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['user'] = '';
$cfg['Servers'][$i]['password'] = '';

/* End of servers configuration */

$cfg['blowfish_secret'] = '5707870738.70703';
$cfg['DefaultLang'] = 'en';
$cfg['ServerDefault'] = 1;
$cfg['UploadDir'] = '';
$cfg['SaveDir'] = '';

startup procedure cluster mysql

startup primary node01

Turn on the MySQL primary server node01

When connecting node01 MySQL does not start. Try restarting the MySQL service

/etc/init.d/mysql stop; pkill -9 mysqld
/etc/init.d/mysql restart

If the MySQL service does not start see the logs to see the startup error

systemctl status mariadb.service -l

[ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1380: Failed to open channel ‘galera’ at ‘gcomm://192.168.0.1,192.168.0.2’: -110 (Connection timed out)

The above error message indicates that it is not possible to connect to the secondary node. The node01 should be started as the primary node, then see if ports 3306 and 4567 are listening

/etc/init.d/mysql stop; pkill -9 mysqld
galera_new_cluster
netstat -nlp | grep mysqld

[ERROR] WSREP: It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster and may not contain all the updates. To force cluster bootstrap with this node, edit the grastate.dat file manually and set safe_to_bootstrap to 1.

The error message above indicates that node01 was not the last one to leave the cluster, it will be necessary to force its initialization by changing the parameter safe_to_bootstrap to 1

vi /var/lib/mysql/grastate.dat
  
safe_to_bootstrap: 1

After changing the above file start the node as primary, the secondary node will re-synchronize with the primary, then see if ports 3306 and 4567 are listening

/etc/init.d/mysql stop; pkill -9 mysqld
galera_new_cluster
netstat -nlp | grep mysqld

[ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():208: Failed to open backend connection: -98 (Address already in use)

The above error indicates that one of the mysql services is running. Kill mysql services before start the mysql service. See if ports 3306 and 4567 are listening after start mysql service

/etc/init.d/mysql stop; pkill -9 mysqld
galera_new_cluster
netstat -nlp | grep mysqld

[ERROR] Job dev-disk-by\x2duuid-d48d88b8\x2dd83f20b788b5.device/start timed out.
Timed out waiting for device dev-disk-by\x2duuid-d48d88b8\x2dd83f20b788b5.device.
Dependency failed for /dev/disk/by-uuid/d48d88b8-4843-462c-94d9-d83f20b788b5.
Job dev-disk-by\x2duuid-d48d88b8\x2dd83f20b788b5.swap/start failed with result ‘dependency’.
Job dev-disk-by\x2duuid-d48d88b8\x2dd83f20b788b5.device/start failed with result ‘timeout’.
mariadb.service start-pre operation timed out. Terminating.

The above error indicates that one of the mysql services is running. Kill mysql services before start the mysql service. See if ports 3306 and 4567 are listening after start mysql service

/etc/init.d/mysql stop; pkill -9 mysqld
galera_new_cluster
netstat -nlp | grep mysqld

[ERROR] Crash recovery failed. Either correct the problem (if it’s, for exampl
e, out of memory error) and restart, or delete tc log and start mysqld with –tc-heuristic-recover={commit|rollback}

The above error indicates that someone transaction not completed and tc.log should be excluded and mysql should start in recovery mode.

rm -f /var/lib/mysql/tc.log
vi /etc/my.cnf.d/server.cnf

[mysqld]
innodb_force_recovery = 1

/etc/init.d/mysql stop; pkill -9 mysqld
galera_new_cluster

InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so thatInnoDB: innodb_force_… is removed.

The above error indicates that mysql can not be started in recovery mode. remove the recovery line from the configuration file.

vi /etc/my.cnf.d/server.cnf

[mysqld]
#innodb_force_recovery = 1

/etc/init.d/mysql stop; pkill -9 mysqld
galera_new_cluster

startup secondary node02

Restart MySQL, see if port 4567 is listening, after node02 synchronizes with node01 port 3306 is listening too

/etc/init.d/mysql stop; pkill -9 mysqld
/etc/init.d/mysql restart
netstat -nlp | grep mysqld

*Upon restarting mysql on node02 the data will be synchronized with node01 using service running on port 4567 (rsync). Real-time synchronization can be seen in the /var/log/messages file

tail -f /var/log/messages

Check Cluster Status

SHOW STATUS WHERE Variable_name REGEXP 'wsrep_connected|wsrep_ready|wsrep_local_state_comment|wsrep_cluster_size|wsrep_last_committed';

amount of concurrent connections mysql

show the current value of current connections on mysql

SHOW VARIABLES LIKE 'max_connections';

to change the value of concurrent mysql connections

SET GLOBAL max_connections=500;

to change permanently the value of concurrent mysql connections, under the [mysqld] section add the following line:

vi /etc/my.cnf.d/server.cnf

[mysqld]
max_connections = 500