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 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

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

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

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

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 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

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

drop all mysql databases

#!/bin/bash
# Remove all mysql databases

echo 'Enter MySQL user'
read MYSQL_USER

echo 'Enter MySQL password'
stty -echo
read MYSQL_PASSWD
stty echo

DATABASES=$( mysql -u $MYSQL_USER -p$MYSQL_PASSWD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database )

for i in ${DATABASES[*]}; do

  if [ $i != 'mysql' ] && [ $i != 'information_schema' ] && [ $i != 'performance_schema' ]; then

    echo $( date +%H:%M ) drop database: $i 
    time mysql -u $MYSQL_USER -p$MYSQL_PASSWD -e "DROP DATABASE $i"
  fi
done

how to view table engine mysql

Show all tables engine

SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE FROM information_schema.TABLES

Show only tables with MyISAM engine

SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE FROM information_schema.TABLES WHERE ENGINE='MyISAM'

Show only tables with InnoDB engine

SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE FROM information_schema.TABLES WHERE ENGINE='InnoDB'

To change table engine for InnoDB

ALTER TABLE tableNameHere ENGINE='InnoDB'