Databases, Systems & Networks

Source: nixCraft

I‘m a new MySQL server user. My server is running on a CentOS Linux. How can I check the number of active MySQL connections on Linux based system?

You can use the following commands on Linux or Unix-like systems:

• mysqladmin status command
• MySQL show status command
• netstat or ss commands

mysqladmin status command example

Open the terminal App or login to the remote server using ssh:

ssh [email protected]

Type the following command to get a short status message from the MySQL server:

mysqladmin status
## OR ##
mysqladmin status -u root -p
## OR ##
mysqladmin status -h db1.dbsysnet.com -u root -p

Sample outputs:

Uptime: 691356  Threads: 5  Questions: 83237956  Slow queries: 102736  Opens: 3585  Flush tables: 1  Open tables: 1019  Queries per second avg: 120.398

MySQL show status command to see open database connections example

First, connect to the your mysql server:

mysql -u root -p

Type the following sql query to see the number of connection attempts to the MySQL serverincludes both failed and successful connection attempts:

mysql> show status like 'Conn%';

Sample outputs:

You can use the following sql command to see the number of currently open connections at mysql> prompt:

mysql> show status like '%onn%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| Aborted_connects         | 7       |
| Connections              | 6304067 |
| Max_used_connections     | 85      |
| Ssl_client_connects      | 0       |
| Ssl_connect_renegotiates | 0       |
| Ssl_finished_connects    | 0       |
| Threads_connected        | 7       | <---- No of currently open connections
+--------------------------+---------+
7 rows in set (0.00 sec)

Lire la suite…

You have configured your MySql replication, next step is to perform maintenance and monitor your replication. Percona Toolkit for MySQL is a collection of advanced command-line tools to perform a variety of MySQL server and system tasks.

The Percona Toolkit allows you to amongst many tasks to verify MySQL replication integrity, efficiently archive rows, find duplicate indexes, summarize MySQL servers, analyze queries from logs and tcpdump, collect vital system information when problems occur.

To get started you need to install Percona Toolkit, I’m using Ubuntu as an OS on my servers, installing the percona toolkit was straight forward

apt-get install percona-toolkit

To get a summary of your databases use the command – pt-mysql-summary

pt-mysql-summary --u=username --p=password

Next you would want to setup a heartbeat table in your database so you can Monitor MySQL replication delay – for that you will use the pt-heartbeat command.

pt-heartbeat -D wpslavedb --create-table --ask-pass --check --master-server-id 1

You can find the manual for the options here.

pt-heartbeat measures replication lag on a MySQL or PostgreSQL server. You can use it to update a master or monitor a replica. mk-heartbeat depends only on the heartbeat record being replicated to the slave, so it works regardless of the replication mechanism. It works at any depth in the replication hierarchy; for example, it will reliably report how far a slave lags its master’s master’s master.

Start daemonized process to update test.heartbeat table on master:

  pt-heartbeat -D wpslavedb --update -h master-server --daemonize --ask-pass

Monitor replication lag on slave:

  pt-heartbeat -D wpslavedb --monitor -h slave-server --ask-pass
  pt-heartbeat -D wpslavedb --monitor -h slave-server --dbi-driver Pg --ask-pass

Check slave lag once and exit (using optional DSN to specify slave host):

  pt-heartbeat -D wpslavedb --check h=slave-server --ask-pass

If the replication hierarchy is “master -> slave1 -> slave2” with corresponding server IDs 1, 2 and 3, you can:

  pt-heartbeat --daemonize -D wpslavedb --update -h master --ask-pass
  pt-heartbeat --daemonize -D wpslavedb --update -h slave1 --ask-pass

Then check (or monitor) the replication delay from master to slave2:

  pt-heartbeat -D wpslavedb --master-server-id 1 --check slave2 --ask-pass

Or check the replication delay from slave1 to slave2:

  pt-heartbeat -D wpslavedb --master-server-id 2 --check slave2 --ask-pass

if you developing and writing SQL statements, this command is very useful pt-query-digest – Analyze MySQL queries from logs, processlist, and tcpdump

  pt-query-digest slow.log    // requires you to have slow query logged enabled

There are other tools Percona Toolkit, these that I shown is what I find useful for my monitoring and maintenance.

If you want to find out the best MySQL configuration for your application, you can head over to Percona’s website they have a MySql wizards.

Source: torbjornzetterlund.com

Source: denisrosenkranz.com

Nous avons vu précédement comment mettre un place un Cluster Apache actif/passif avec DRBD et HeartBeat. Dans ce tutoriel nous allons voir comment faire la même chose mais avec MySQL.

La configuration de DRBD et de Heartbeat est la même que pour le tutoriel précédent.

Voilà ce que nous allons mettre en place

Donc pour faire ce tutoriel suivez le tutoriel suivant jusqu’a « Installation d’apache« :

Un cluster DRBD/Apache avec Heartbeat sur Debian 7

Lire la suite…

Nous sommes dans la situation où il faut redémarrer le serveur maitre

connection ssh sur les deux serveurs:

sur le maitre
#ssh maitre@IP

Arrêt du service heartbeat sur les deux serveurs

# service heartbeat stop
ou
#/etc/init.d/heartbeat stop

Même chose sur l’esclave
A partir de là, plus personne n’a accès à l’application web

MySql:

on va se placer dans votre homedir sur les deux serveurs.
#cd /home

sur le serveur esclave, on va récupérer la base mysql et la copier sur le serveur maître:

[esclave]# mysqldump -u root -pMDP votre_bdd > votre_bdd.sql
[esclave]# scp votre_bdd.sql maitre@IP:/home

Passons sur le serveur maître:

[maitre]#cd /home

on injecte la base:
[maitre]# mysql -u root -pMDP votre_bdd.sql > votre_bdd.sql

Maintenant, on va activer la réplication de la base MySql du serveur maître sur l’esclave: sur le serveur maître: tout d’abord on va se connecter sur la base

[maitre]# mysql -u root -pMDP

Puis on bloque la base en lecture seule
mysql>: FLUSH TABLES WITH READ LOCK;

On repère la position de la base:

mysql > SHOW MASTER STATUS;
  ------------------ ---------- -------------- -------------------------- 
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |
  ------------------ ---------- -------------- -------------------------- 
 | mysql-bin.000003 | 73       | test,bar     | foo,manual,mysql         |
  ------------------ ---------- -------------- -------------------------- 
 1 row in set (0.06 sec)

et on note:
mysql-bin.000003 ← le log bin à utiliser
73 ← la position du log
évidemment à adapter suivant le cas.

sur le serveur esclave:
on se connecte aussi à la base mysql
[esclave]# mysql -u root -pMDP

on stoppe la réplication en cours
mysql>stop slave;

on fait une RAZ de la réplication
mysql>reset slave;

On va positionner le serveur esclave comme le maître:
mysql> CHANGE MASTER TO MASTER_HOST = 'IP', MASTER_USER = 'repli', MASTER_PASSWORD = 'repli', MASTER_LOG_FILE = 'mysql-bin.000003', MASTER_LOG_POS = 73;

on démarre la réplication
mysql> start slave;

on vérifie qu’on n’a pas d’erreur

mysql> show slave statusG
 *************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 10.94.8.58
 Master_User: repli
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000003
 Read_Master_Log_Pos: 73
 Relay_Log_File: GLPI_esclave-relay-bin.000024
 Relay_Log_Pos: 660186
 Relay_Master_Log_File: mysql-bin.000003
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 ******
 Last_Errno: 0
 Last_Error:
 ******
 Seconds_Behind_Master: 0
 1 row in set (0.00 sec)

on se déconnecte de mysql
mysql>exit;

sur le serveur maitre on déverrouille l’écriture sur la base
mysql> UNLOCK TABLES;

on se déconnecte de mysql
mysql>exit;

et pour finir on lance le script de lancement automatique des services heartbeat/mon/http/myslq:

[maitre]#./startHA

sur le serveur maitre c’est le même script sauf le service Mon en moins:
[esclave]#./startHA

on vérifie que l’IP virtuel est actif sur le serveur maitre:

[maitre]#ifconfig

eth0:0 Link encap:Ethernet HWaddr 00:22:19:D7:73:48
 inet adr:10.94.8.56 Bcast:10.94.15.255 Masque:255.255.248.0
 UP BROADCAST RUNNING MULTICAST MTU:1492 Metric:1
 Interruption:16

et dans le navigateur firefox, on fait un test de connection:

http://appliweb.com

SI TABLE CRASHED :

d’après le log, on repère la table dite crashed

connexion à mysql :
#mysql -u root -pMDP ;

on sélectionne la base
mysql>use votre_bdd ;

on répare la table
mysql>repair table nomdelatable ;

on sort de mysql
mysql>exit;