MySQL

安装

rsync -avcP ~/Downloads/mysql-server_5.6.23-1debian7_amd64.deb-bundle.tar root@192.168.16.33:/root/mysql/
#scp root@192.168.16.249:/vmfs/volumes/249-data1/mysql-server_5.6.23-1debian7_amd64.deb-bundle.tar /root/mysql/
wget -c http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-server_5.6.25-1debian7_amd64.deb-bundle.tar 注意版本号
sudo apt-get install libaio1

tar xf mysql-server_5.6.25-1debian7_amd64.deb-bundle.tar
dpkg -i mysql-common_5.6.25-1debian7_amd64.deb
dpkg -i mysql-community-server_5.6.25-1debian7_amd64.deb
dpkg -i mysql-community-client_5.6.25-1debian7_amd64.deb
apt-get -f -y install
更换目录后记得mysql_install_db,service mysql restart,mysql_secure_installation

配置文件

Debian

mv /etc/mysql/my.cnf /etc/mysql/my.cnf.back
vim /etc/mysql/my.cnf
[client]
port                = 3306
socket              = /var/run/mysqld/mysqld.sock
 
[mysqld_safe]
socket              = /var/run/mysqld/mysqld.sock
nice                = 0
 
[mysqld]
skip-external-locking
skip-name-resolve
user                = mysql
pid-file            = /srv/mdata/mysqld.pid
socket              = /var/run/mysqld/mysqld.sock
port                = 3306
basedir             = /usr
datadir             = /srv/mdata
tmpdir              = /tmp
language            = /usr/share/mysql/english
 
server-id           = 1
log_bin             = /srv/mdata/mysql-bin.log
expire_logs_days    = 10
max_binlog_size     = 100M
#binlog_do_db        = www71daicom
#binlog_do_db        = pm
binlog_ignore_db    = mysql

explicit_defaults_for_timestamp
wait-timeout=60
bind-address    = 127.0.0.1
log-error   = /var/log/mysql/error.log
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
symbolic-links=0
# optimize for low memory
key_buffer=16K
table_open_cache=4
query_cache_limit=256K
query_cache_size=4M
max_allowed_packet=1M
sort_buffer_size=64K
read_buffer_size=256K
thread_stack=64K
# only local
skip-networking

innodb_buffer_pool_size=5M
innodb_log_buffer_size=256K
query_cache_size=0
max_connections=10
key_buffer_size=8
thread_cache_size=0
host_cache_size=0
innodb_ft_cache_size=1600000
innodb_ft_total_cache_size=32000000

# per thread or per operation settings
thread_stack=131072
sort_buffer_size=32K
read_buffer_size=8200
read_rnd_buffer_size=8200
max_heap_table_size=16K
tmp_table_size=1K
bulk_insert_buffer_size=0
join_buffer_size=128
net_buffer_length=1K
innodb_sort_buffer_size=64K

#settings that relate to the binary log (if enabled)
binlog_cache_size=4K
binlog_stmt_cache_size=4K




 
[mysqldump]
quick
quote-names
max_allowed_packet  = 16M
 
[isamchk]
key_buffer          = 16M
 
!includedir /etc/mysql/conf.d/

CentOS

mv /etc/my.cnf /etc/my.cnf.cnf.back
vim /etc/my.cnf
[mysqld]
skip-name-resolve
datadir             = /var/lib/mysql
socket              = /var/lib/mysql/mysql.sock
user                = mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links      = 0
#bind-address       = 127.0.0.1
server-id           = 2
log_bin             = /var/lib/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size     = 100M
binlog_do_db        = www71daicom
binlog_ignore_db    = mysql
binlog_cache_size   = 1M
tmp_table_size      = 32M
max_heap_table_size = 32M
long_query_time     = 2
log_slow_queries    = 1
query_cache_limit   = 8M
query_cache_type    = 1
query_cache_size    = 8M
key_buffer_size     = 16M
table_open_cache    = 128
thread_cache_size   = 2

[mysqld_safe]
log-error           = /var/log/mysqld.log
pid-file            = /var/run/mysqld/mysqld.pid

Master

mysql -uroot -e 'SHOW MASTER STATUS;'
mysql -uroot -e 'FLUSH TABLES WITH READ LOCK;'
mysqldump -uroot pm > pm_mysqldump.sql
mysql -uroot -e 'RESET MASTER;'
mysql -uroot -e 'SHOW MASTER STATUS;'
mysql -uroot -e 'SHOW MASTER STATUS\G' > pm_master.log
mysql -uroot -e 'UNLOCK TABLES;'
mysql -uroot -e 'GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO slave@"118.244.213.192" IDENTIFIED BY "***";'
mysql -uroot -e 'GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO slave@"58.247.45.102" IDENTIFIED BY "***";'
mysql -uroot -e 'FLUSH PRIVILEGES;'

Salve

mysql -uroot -e 'SHOW SLAVE STATUS\G'
mysql -uroot -e 'STOP SLAVE;'
mysql -uroot -e 'RESET SLAVE;'
mysql -uroot -e 'DROP DATABASE pm;'
mysql -uroot -e 'CREATE DATABASE pm;'
mysql -uroot pm < pm_mysqldump.sql
mysql -uroot -e 'CHANGE MASTER TO \
MASTER_HOST="112.124.117.46",\
MASTER_USER="slave",
MASTER_PASSWORD="***",\
MASTER_PORT=3306,\
MASTER_LOG_FILE="mysql-bin.000001",\
MASTER_LOG_POS=106;'
mysql -uroot -e 'START SLAVE;'
mysql -uroot -e 'SHOW SLAVE STATUS\G'