Difference between revisions of "Dbs"

Latest revision as of 20:27, 23 January 2017

MyISAM check

myisamchk -r -f /var/lib/mysql/visitkat_main/*.MYI

MYSQL replica

Master ini:

log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1 
sync_binlog=1
expire_log_days=4
##SET GLOBAL expire_logs_days = 4;

Slave ini:

datadir="E:/MySQL/data/" 
server-id=2
sync_binlog=1
##SET GLOBAL expire_logs_days = 4;

Run on the master:

mysql master> CREATE USER 'user_name'@'ip.of.the.slave' IDENTIFIED BY 'password';
mysql master> GRANT REPLICATION SLAVE ON *.* TO 'user_name'@'ip.of.the.slave';    
mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A -uroot -p > z:\dbdata.db

Run on the slave:

Get-Content E:\dbdata.db -TotalCount 25
mysql -uroot -p <e:\dbdata.db

-- CHANGE MASTER TO MASTER_LOG_FILE = 'file_name', MASTER_LOG_POS = file_pos;

mysql slave> CHANGE MASTER TO MASTER_HOST = 'ip.of.the.master',
                MASTER_USER = 'user_name_you_just_created',
                MASTER_PASSWORD = 'password_you_just_defined',
                MASTER_LOG_FILE = 'file_name',
                MASTER_LOG_POS = file_pos;

mysql slave> START SLAVE;
mysql slave> SHOW SLAVE STATUS;

1. Slave_IO_Running: Yes
2. Slave_SQL_Running: Yes

Check storage engine

cat <<'SCRIPT' >/root/innoDBcheck.sh
while read db; do
printf "$db\t\t"
if (mysql -e "use $db; show table status;" | grep -qm 1 InnoDB); then
        ((++innoC))
        echo -e "\e[1;91minnoDB\e[0m"
else
        ((++nonInnoC))
        echo -e "\e[1;92mnone-innoDB\e[0m"
fi
done < <(mysql -e "show databases;" | grep -v -- '----\|Database\|information_schema' | sed -e 's/|//g')
for var in {1..50}; do printf '='; done; echo
printf "Default Database engine: "; echo -en "\e[1;93m"; mysql -e "show storage engines;" | grep DEFAULT | awk '{print $1}'; echo -ne "\e[0m"
echo "Number of total DBs: $((innoC+nonInnoC))"
echo "Number of DB(s) using InnoDB: $innoC"
echo "Number of DB(s) NOT using InnoDB: $nonInnoC"
SCRIPT

chmod 700 /root/innoDBcheck.sh
/root/innoDBcheck.sh

Convert InnoDB to MyIsam

DB=mydb_latest && mysqldump $DB>/root/$DB.original.sql && mysqldump $DB | sed -e 's/^) ENGINE=InnoDB/) ENGINE=MyISAM/' >/root/$DB.sql && mysql $DB </root/$DB.sql

Mysql tuner

mysql -e "status" | grep Uptime; mysql -e "show status;" | grep Max_used_connections; read -n 1 -p "Press any key to continue..." tmp; wget --no-check-certificate https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl && chmod 700 mysqltuner.pl && ./mysqltuner.pl

Check and optimize tables

mysqlcheck --auto-repair --optimize --all-databases

Clear exim stats

mysql

mysql> use eximstats
mysql> delete from sends;
mysql> delete from smtp;
mysql> delete from failures;
mysql> delete from defers;