mysql57
Mysql 安装实战精讲（大神必经之路）
rpm -qa|grep mariadb
yum remove mariadb-libs-5.5.60-1.el7.x86_64
rpm -qa|grep mariadb
cd /tmp/
wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
yum -y localinstall mysql57-community-release-el7-11.noarch.rpm
yum -y install mysql-community-server
systemctl start mysqld //启动数据库
设置mysql为系统服务，随系统启动而启动
systemctl enable mysqld
systemctl daemon-reload
cat /var/log/mysqld.log
vi /etc/my.cnf
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | # For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
back_log = 500
wait_timeout = 1800
max_connections = 3000
max_user_connections = 800
innodb_buffer_pool_size = 1024M
lower_case_table_names=1
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
|
systemctl restart mysqld //重新启动生效
grep 'temporary password' /var/log/mysqld.log //查看密码
mysql -u root -p
- mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
- mysql> GRANT ALL PRIVILEGES ON *.* TO 'remote'@'%' IDENTIFIED BY '12345678' WITH GRANT OPTION;
- GRANT ALL PRIVILEGES ON *.* TO 'XXX'@'%' IDENTIFIED BY 'xxx.xxx.xxx.xxx' WITH GRANT OPTION;
- FLUSH PRIVILEGES;
- select host,user from mysql.user; delete from user where user='用户名'
crontab -l
systemctl list-unit-files
hostnamectl set-hostname ixiaoguo-1
cat /etc/hosts
cat /etc/hostname
vi /etc/profile
systemctl restart mysqld
从数据库安装配置
rpm -qa|grep mariadb
yum remove mariadb-libs-5.5.52-1.el7.x86_64
cd /tmp/
wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
yum -y localinstall mysql57-community-release-el7-11.noarch.rpm
yum -y install mysql-community-server
systemctl start mysqld
cat /var/log/mysqld.log
mysql -u root -p
crontab -l
systemctl list-unit-files
netstat -npl
hostnamectl set-hostname MySQL-slave
cat /etc/hosts
vim /etc/hostname
vim /etc/sysconfig/network
vi /etc/my.cnf
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | # For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
server-id = 164 #服务器IP地址
innodb_flush_log_at_trx_commit = 2
sync_binlog = 1
log-bin = mysql-bin-164
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
|
systemctl restart mysqld
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
mysql> start slave;
wget --no-check-certificate -O shadowsocks-all.sh https://raw.githubusercontent.com/teddysun/shadowsocks_install/master/shadowsocks-all.sh
netstat -npl
cat shadowsocks-all.sh
chmod +x shadowsocks-all.sh
./shadowsocks-all.sh 2>&1 | tee shadowsocks-all.log
netstat -npl
数据库冷备份
mkdir -p /data/mysqlbak/data
mkdir -p /data/mysqlbak/scripts
mkdir -p /data/mysqlbak/logs
vim /data/mysqlbak/scripts/backup.sh
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | #!/bin/bash
#备份目录
BACKUP_ROOT=/data/mysqlbak
BACKUP_FILEDIR=$BACKUP_ROOT/data
#当前日期
DATE=$(date +%Y%m%d)
######备份######
#查询所有数据库
#-uroot -p123456表示使用root账号执行命令，且root账号的密码为:123456 排除自带数据库
DATABASES=$(mysql -ukong -pkong123 -e "show databases" | grep -Ev "Database|sys|information_schema|performance_schema|mysql")
#循环数据库进行备份
echo "array is:${DATABASES[@]}"
echo "array length is:${#DATABASES[*]}"
echo "for--begin"
for db in $DATABASES
do
echo
echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz BEGIN----------
mysqldump -ukong -pkong123 --default-character-set=utf8 -q --lock-all-tables --flush-logs -E -R --triggers -B ${db} | gzip > $BACKUP_FILEDIR/${db}_$DATE.sql.gz
echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz COMPLETE----------
echo ----------scp 130.8 begin----------
expect -c "
spawn scp -r /data/mysqlbak/data/${db}_$DATE.sql.gz root@31.121.130.8:/data/mysqlbak/data11/
expect {
\"*assword\" {set timeout 300; send \"此处是scp的密码\r\"; exp_continue;}
\"yes/no\" {send \"yes\r\";}
}
expect eof"
echo ----------scp 130.8 end----------
echo
done
echo "for--done"
|
chmod 777 backup.sh
vim /data/mysqlbak/scripts/backup_clean.sh
| 1 2 3 4 | #!/bin/bash
echo ----------CLEAN BEGIN----------
find /data/mysqlbak/data -mtime +7 -name "*.gz" -exec rm -rf {} \;
echo ----------CLEAN COMPLETE----------
|
chmod 777 backup_clean.sh
crontab -e
| 1 2 | 25 13 * * * /data/mysqlbak/scripts/backup.sh > /data/mysqlbak/logs/backup.log 2>&1
02 13 * * * /data/mysqlbak/scripts/backup_clean.sh > /data/mysqlbak/logs/backup_full_clean.log 2>&1
|
crontab -l
yum install expect
扫一扫
3115
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
