Meadmin: Created page with " == 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..."

2017-01-23T20:27:53Z

Created page with " == 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..."

New page

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

Dbs - Revision history

Meadmin: Created page with " == 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..."