Mysql Notes

From Urandom
Jump to: navigation, search

Just some mysql notes i find useful

Contents

Mysql Replication related

Master-Master or Master-Slave configuration

/etc/my.conf

auto_increment_increment=Y # where Y is the total amount of servers
auto_increment_offset=X # where X is the server number (e.g 1,2,3...)
server-id       = Z # where Z is the server number (e.g 1,2,3...)
#In mysql 5.6 and later dont use the master-* flags. 
master-host     =   master.fleet.zone # set to the master hostname
master-user     =   Username #replication user
master-password =   Password #replication password
master-port     =  3306
log-bin=mysql-bin
binlog_format=mixed
binlog-ignore-db=db1 #skip replication db (master, these wont be replicated to slaves)
binlog-ignore-db=db2
binlog-ignore-db=dbN
relay-log=slavehostname-relay-bin
expire_logs_days=14 #this is how long the bin-log is kept

#not mandatory, but use utf8
default-character-set=utf8
default-collation=utf8_unicode_ci
character-set-server=utf8
collation-server=utf8_unicode_ci
init-connect='SET NAMES utf8'

To add replication users use

grant replication slave on *.* to 'replication'@SLAVEHOST identified by 'Password';

To add replication client for monitoring

grant replication client on *.* to 'replication'@ACCESSHOST identified by 'Password';
  • enables to use show slave/master status command.

Cluster synchronization

Start the slave from replication or stop it.

stop slave; 
start slave;

Skiping slave replication errors

To skip errors in replication use

stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave;
  • You can replace 1 with a larger number, but then be sure to run
stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 0; start slave;
  • Then you wont skip errorors later on and they go un noticed.

Change slave position

If for some reason the slave replicates the master from an unwanted position, you can change the current position. This might be acceptable approach for a master-to-master replication.

stop slave;
CHANGE MASTER TO MASTER_HOST = 'MasterHost', MASTER_USER = 'Username', MASTER_PASSWORD = 'Password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 1;
SLAVE START;

Live clone new slave or restore slave from scrach

Reset Master log

  • We do this so replication will start from a new point
RESET MASTER;

Dump the master database

MYSQL_CONN="-hhostip -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 1
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

mysqldump ${MYSQL_CONN} --master-data=2 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

Database recovery

Fix broken or corrupt tables

mysqlcheck -u root -p --auto-repair -c --all-databases
* Can also be combined with -o as in optimize.

Check for broken or corrupt tables

mysqlcheck -c -u root -p --all-databases

Converting databases

Convert all tables to UTF-8

read -p"What is the mysql username?: " USER
read -p"What is the mysql password?: " PASS
read -p"What is the mysql database to convert?: " DATAB
mysql -u $USER -p$PASS --database=$DATAB -B -N -e "SHOW TABLES" | awk '{print "ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;"}' | mysql-u $USER -p$PASS --database=$DATAB

Convert all tables to InnoDB

read -p"What is the mysql username?: " USER
read -p"What is the mysql password?: " PASS
read -p"What is the mysql database to convert?: " DATAB
mysql -u $USER -p$PASS --database=$DATAB -B -N -e "SHOW TABLES" | awk '{print "ALTER TABLE", $1, "ENGINE = InnoDB;"}' | mysql-u $USER -p$PASS --database=$DATAB


Compare two mysql servers databases tables and count rows

Usage

Create file to home folder ~/.my.cnf or add to your main /etc/my.cnf this is for authentication. Boths servers should have the same login aswell.

[client]
user=root 
password="XXXXXXXXX"

Run script

chmod +x mysql_row_count.sh
./mysql_row_count.sh mysqhost1 mysqlhost2

Script mysql_row_count.sh

#!/bin/bash


#Martin Vool 2016
   
if [ "$1" == "" -o "$2" == "" ]   
then        
   
echo "Specify databases as input, for example mysql_row_count.sh dbtll201t.fleet.zone dbtll202t.fleet.zone"
exit 1      
fi 
   
DB_A=$1     
DB_B=$2     
   
DBSHOW="SHOW DATABASES WHERE \`Database\` NOT IN ('performance_schema', 'lost+found', '#mysql50#lost+found')"
   
DB_A_DBS=`mysql -h $DB_A -B -N -e "$DBSHOW"`
DB_B_DBS=`mysql -h $DB_B -B -N -e "$DBSHOW"`
echo "Checking if $DB_B is the same as $DB_A"
for dbadb in $DB_A_DBS
do 
  found=0   
  for dbbdb in $DB_B_DBS 
  do        
    if [ "$dbadb" == "$dbbdb" ]   
    then    
      found=1         
      break 
    fi      
  done      
  if [ $found -eq 0 ] 
  then      
    echo "$DB_B $dbadb MISSING"   
  else      
    DB_A_TBLS=`mysql -h $DB_A --database=information_schema -B -N -e "SELECT TABLE_NAME FROM TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='$dbadb'"`
    DB_B_TBLS=`mysql -h $DB_B --database=information_schema -B -N -e "SELECT TABLE_NAME FROM TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='$dbadb'"`
    for DB_A_table in $DB_A_TBLS
    do
      tfound=0
      for DB_B_table in $DB_B_TBLS
      do
        if [ "$DB_B_table" == "$DB_A_table" ]
        then
          tfound=1
          break
        fi
      done
      if [ $tfound -eq 0 ]
      then
        echo "$DB_B ${dbadb}.${DB_A_table} MISSING TABLE $DB_A_table"
      else
        sql="select count(*) from \`${dbadb}\`.${DB_A_table}"
        A_COUNT=`mysql -h $DB_A -B -N -e "$sql"`
        B_COUNT=`mysql -h $DB_B -B -N -e "$sql"`
        if [ $A_COUNT -ne $B_COUNT ]
        then
          echo "$DB_B ${dbadb}.${DB_A_table} ROWS MISMATCH $B_COUNT VS $A_COUNT"
        fi
      fi
    done

  fi
done
echo "###################################################################################"
echo "Checking if $DB_A is the same as $DB_B"

for dbbdb in $DB_B_DBS
do
  found=0
  for dbadb in $DB_A_DBS
  do
    if [ "$dbadb" == "$dbbdb" ]
    then
      found=1
      break
    fi
  done
  if [ $found -eq 0 ]
  then
    echo "$DB_A $dbbdb MISSING"
  else
    DB_A_TBLS=`mysql -h $DB_A --database=information_schema -B -N -e "SELECT TABLE_NAME FROM TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='$dbbdb'"`
    DB_B_TBLS=`mysql -h $DB_B --database=information_schema -B -N -e "SELECT TABLE_NAME FROM TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='$dbbdb'"`
    for DB_B_table in $DB_B_TBLS
    do
      tfound=0
      for DB_A_table in $DB_A_TBLS
      do
        if [ "$DB_B_table" == "$DB_A_table" ]
        then
          tfound=1
          break
        fi
      done
      if [ $tfound -eq 0 ]
      then
        echo "$DB_A ${dbbdb}.${DB_B_table} MISSING TABLE $DB_B_table"
      else
        sql="select count(*) from \`${dbbdb}\`.${DB_B_table}"
        A_COUNT=`mysql -h $DB_A -B -N -e "$sql"`
        B_COUNT=`mysql -h $DB_B -B -N -e "$sql"`
        if [ $A_COUNT -ne $B_COUNT ]
        then
          echo "$DB_A ${dbbdb}.${DB_B_table} ROWS MISMATCH $A_COUNT VS $B_COUNT"
        fi

      fi
    done

  fi
done
Personal tools