Linux运维笔记|自动化运维攻城狮

MySQL 5.7 编译安装主从

mysql5.7   主从  

创建安装目录

groupadd mysql  
useradd -g mysql -s /sbin/nologin -M mysql  
mkdir -p /data/etc  
mkdir -p /data/db/mysql  
mkdir -p /data/db/mysql/binlog  
mkdir -p /data/soft/mysql  
mkdir -p /tmp/mysql/  
mkdir -p /tmp/mysql-slave/  
chown mysql.mysql /data/db/ -R  
chown mysql.mysql /tmp/mysql* -R

启动脚本

cd /etc/init.d/  
wget -q https://ops.qe23.com/soft/config/mysqld  
wget -q https://ops.qe23.com/soft/config/mysqld-slave  
chmod +x /etc/init.d/mysqld  
chmod +x /etc/init.d/mysqld-slave  
chkconfig mysqld on  
chkconfig mysqld-slave on  
chkconfig --add mysqld  
chkconfig --add mysqld-slave

编译安装mysql-5.7.28

cd mysql-5.7.28/  
cmake . \  
-DCMAKE_INSTALL_PREFIX=/data/soft/mysql \
-DMYSQL_DATADIR=/data/db/mysql \
-DWITH_BOOST=boost \
-DMYSQL_UNIX_ADDR=/tmp/mysql/mysql.sock \
-DMYSQL_TCP_PORT=3306 \
-DSYSCONFDIR=/data/etc \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DENABLE_DTRACE=0 \
-DDEFAULT_CHARSET=utf8mb4 \
-DDEFAULT_COLLATION=utf8mb4_general_ci \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_SSL=yes \
-DEXTRA_CHARSETS=all

make -j8  
make install

cd mysql-5.7.28/  
cmake . \  
-DCMAKE_INSTALL_PREFIX=/data/soft/mysql-slave \
-DMYSQL_DATADIR=/data/db/mysql-slave \
-DWITH_BOOST=boost \
-DMYSQL_UNIX_ADDR=/tmp/mysql-slave/mysql.sock \
-DSYSCONFDIR=/data/etc-slave \
-DMYSQL_TCP_PORT=3307 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DENABLE_DTRACE=0 \
-DDEFAULT_CHARSET=utf8mb4 \
-DDEFAULT_COLLATION=utf8mb4_general_ci \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_SSL=yes \
-DEXTRA_CHARSETS=all
make -j8  
make install

配置文件

  • /data/soft/mysql/my.cnf 
[mysql]
default-character-set=utf8  
host = localhost  
socket=/tmp/mysql/mysql.sock

[mysqld]
user            = mysql  
bind-address = 127.0.0.1  
socket=/tmp/mysql/mysql.sock  
basedir=/data/soft/mysql  
datadir=/data/db/mysql/data  
log-bin=/data/db/mysql/binlog/binlog  
expire_logs_days = 7  
server-id=1  
#skip-name-resolve
max_connections=2000  
default_storage_engine          = InnoDB  
character_set_server            = utf8

# INNODB OPTIONS # 
innodb_buffer_pool_size         = 2G  
innodb_log_file_size            = 256M  
innodb_log_buffer_size          = 32M  
innodb_flush_log_at_trx_commit  = 2  
innodb_file_per_table           = 1  
innodb_open_files               = 2048

# CACHES AND LIMITS # 
tmp_table_size                  = 256M  
max_heap_table_size             = 256M  
query_cache_type                = 0  
query_cache_size                = 0  
thread_cache_size               = 32  
open_files_limit                = 65535  
table_definition_cache          = 4096  
table_open_cache                = 2048  
interactive_timeout             = 31536000  
wait_timeout                    = 31536000

# MyISAM # 
key_buffer_size                 = 32M  
#myisam_recover                  = FORCE,BACKUP

# SAFETY # 
max_allowed_packet              = 512M  
max_connect_errors              = 10000  
sql_mode                        = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION  
innodb                          = FORCE

[mysqldump]
quick  
max_allowed_packet = 512M

[mysql]
no-auto-rehash  
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 128M  
sort_buffer_size = 128M  
read_buffer = 4M  
write_buffer = 4M

[mysqlhotcopy]
interactive-timeout
  • /data/soft/mysql-slave/my.cnf 
[mysql]
default-character-set=utf8  
host = localhost  
socket=/tmp/mysql-slave/mysql.sock

[mysqld]
user            = mysql  
bind-address = 127.0.0.1  
socket=/tmp/mysql-slave/mysql.sock  
basedir=/data/soft/mysql-slave  
datadir=/data/db/mysql-slave/data  
log-bin=/data/db/mysql-slave/binlog  
expire_logs_days = 7  
server-id=2  
#skip-name-resolve
max_connections=2000  
default_storage_engine          = InnoDB  
character_set_server            = utf8

# INNODB OPTIONS # 
innodb_buffer_pool_size         = 1G  
innodb_log_file_size            = 256M  
innodb_log_buffer_size          = 32M  
innodb_flush_log_at_trx_commit  = 2  
innodb_file_per_table           = 1  
innodb_open_files               = 2048

# CACHES AND LIMITS # 
tmp_table_size                  = 256M  
max_heap_table_size             = 256M  
query_cache_type                = 0  
query_cache_size                = 0  
thread_cache_size               = 32  
open_files_limit                = 65535  
table_definition_cache          = 4096  
table_open_cache                = 2048  
interactive_timeout             = 31536000  
wait_timeout                    = 31536000

# MyISAM # 
key_buffer_size                 = 32M  
#myisam_recover                  = FORCE,BACKUP

# SAFETY # 
max_allowed_packet              = 512M  
max_connect_errors              = 10000  
sql_mode                        = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION  
innodb                          = FORCE

[mysqldump]
quick  
max_allowed_packet = 512M

[mysql]
no-auto-rehash  
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 128M  
sort_buffer_size = 128M  
read_buffer = 4M  
write_buffer = 4M

[mysqlhotcopy]
interactive-timeout

初始化

/data/soft/mysql/bin/mysqld --defaults-file=/data/soft/mysql/my.cnf --user=mysql --basedir=/data/soft/mysql/  --initialize-insecure

/data/soft/mysql-slave/bin/mysqld --defaults-file=/data/soft/mysql-slave/my.cnf --user=mysql --basedir=/data/soft/mysql-slave/  --initialize-insecure

centos7 安装完登陆会提示Segmentation fault (core dumped)

  • 修改文件：vi mysql-5.7.28/cmd-line-utils/libedit/terminal.c
    • 把terminalset方法中的 char buf[TCBUFSIZE]; 这一行注释
    • 再把 area = buf;改为 area = NULL;
    • 重新make -j8 && make install

主从配置

mysql -uroot -pP4kLfYWIpl11AQFeWm -e "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1' IDENTIFIED BY '123456';"  
mysql -uroot -p123456 -e "GRANT ALL ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY '123456';"  
mysql -uroot -p123456 -e "flush privileges;"  
bin_log_name=$(mysql -uroot -p123456 -e "show master status;"|tail -n1|awk '{print $1}')  
log_pos_name=$(mysql -uroot -p123456 -e "show master status;"|tail -n1|awk '{print $2}')

/data/soft/mysql-slave/bin/mysql -uroot -p123456 -e "GRANT ALL ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY '123456';"
/data/soft/mysql-slave/bin/mysql -uroot -p123456 -e "GRANT ALL ON *.* TO 'query'@'%' IDENTIFIED BY '123456';"
/data/soft/mysql-slave/bin/mysql -uroot -p123456 -e "flush privileges;"
/data/soft/mysql-slave/bin/mysql -uroot -p123456 -e  'stop slave;'
/data/soft/mysql-slave/bin/mysql -uroot -p123456 -e  'reset slave;'
/data/soft/mysql-slave/bin/mysql -uroot -p123456 -e  "change master to master_user='repl', master_password='123456', master_host='127.0.0.1',master_port=3306, master_log_file='"$bin_log_name"',master_log_pos=$log_pos_name;"
/data/soft/mysql-slave/bin/mysql -uroot -p123456 -e  'start slave;'