CMALL商城——目录

卸载

rpm -qa | grep mysql

rpm -ev bt-mysql56-5.6.44-1.el7.x86_64 --nodeps

rpm -qa | grep mariadb

rpm -e --nodeps mariadb-libs-5.5.64-1.el7.x86_64

wget  mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz /usr/local/cd /usr/local/tar -xvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gzmv mysql-5.7.28-linux-glibc2.12-x86_64 mysqlgroupadd mysqluseradd -r -g mysql mysqlmkdir -p /data/mysqlchown mysql:mysql -R /data/mysql

cd /usr/local/mysql/bin/su - mysql./mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/data/mysql/ --user=mysql –initializecat /data/mysql/mysql.errcp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

启动服务

service mysql start

./mysql -u root -p

默认密码：

SET PASSWORD = PASSWORD('123456');ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;FLUSH PRIVILEGES;use mysql;update user set host = '%' where user = 'root';FLUSH PRIVILEGES;ln -s  /usr/local/mysql/bin/mysql    /usr/binln -s  /usr/local/mysql/bin/mysqldump    /usr/bin

update user set authentication_string=password('123456') where user='root';alter user 'root'@'%' identified by '123456';update user set password=password('123456') where user='root';update user set password=password('123456') where user='cmall_dev';alter user 'cmall_prod'@'%' identified by '123456';update user set password=password('123456') where user='cmall_sim';flush privileges;

vi /etc/my.cnf

mysql -u root -pcreate user 'cmall_prod'@'%' identified by '123456';FLUSH PRIVILEGES;CREATE DATABASE IF NOT EXISTS cmall_prod DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;grant all privileges on cmall_prod.* to 'cmall_prod'@'%' identified by '123456';create user 'cmall_slave_prod'@'%' identified by '123456';FLUSH PRIVILEGES;CREATE DATABASE IF NOT EXISTS cmall_slave_prod DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

show variables like '%connections%';

show global status like '%connections%';

1.1.1. 导入导出

导出：

mysqldump -uroot -p123456 --default-character-set=utf8mb4 -q --lock-all-tables --flush-logs -E -R --triggers -B ${db} | gzip > $BACKUP_FILEDIR/${db}_$DATE.sql.gz停止复制：stop slave;解压：gunzip -r cmall_prod_202001300300.sql.gz修改数据库名称：vi cmall_prod_202002131500.sql

导入：

mysql -uroot -p123456 < cmall_prod_202002131500.sql

source导入：use cmall_imp;source cmall_imp.sql;

vi /mysql/backup/scripts/backup_full.sh

#!/bin/bash#备份目录BACKUP_ROOT=/data/mysql/backupBACKUP_FILEDIR=$BACKUP_ROOT/filesBACKUP_LOGDIR=$BACKUP_ROOT/logs#当前日期DATE=$(date +%Y%m%d)######备份#######查询所有数据库#-uroot -p123456表示使用root账号执行命令，且root账号的密码为:123456DATABASES=$(mysql -uroot -p123456 -e "show databases" | grep -Ev "Database|sys|information_schema")#DATABASES=$(mysql -uroot -p123456 -e "SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN ('sys','mysql','information_schema','performance_schema');" | grep -v "SCHEMA_NAME","ken.io")echo $DATABASES#循环数据库进行备份for db in $DATABASESdoechoecho ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz BEGIN----------mysqldump -uroot -p123456 --default-character-set=utf8mb4 -q --lock-all-tables --flush-logs -E -R --triggers -B ${db} | gzip > $BACKUP_FILEDIR/${db}_$DATE.sql.gzecho ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz COMPLETE----------echodoneecho "done"

#!/bin/bashfind /mysql/backup/files -mtime +7 -name "*.gz" -exec rm -rf {} \;

安装crontabyum install -y crontab#添加定时任务crontab -e#每天凌晨3点执行00 3 * * * sh /data/mysql/backup/scripts/backup_full.sh00 15 * * * sh /data/mysql/backup/scripts/backup_full.sh#查看定时任务crontab –l

对于自建MySQL，需要先开启Binlog写入功能，配置binlog-format为ROW模式，通过修改MySQL配置文件来开启bin_log，使用find / -name my.cnf查找my.cnf，修改文件内容如下：

[mysqld]log-bin=mysql-bin # 开启binlogbinlog-format=ROW # 选择ROW模式server_id=1 # 配置MySQL replaction需要定义，不要和Canal的slaveId重复

创建链接MySQL的账号Canal并授予作为MySQL slave的权限，如果已有账户可直接GRANT：

mysql -uroot -proot# 创建账号CREATE USER canal IDENTIFIED BY 'canal'; # 授予权限GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';-- GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ;# 刷新并应用FLUSH PRIVILEGES;

show variables like '%time_zone%';

// 设置默认时区default-time_zone='+8:00'

show full processlist;

show variables like '%slow%';

CMALL商城——目录

如有帮助，烦请点赞、关注。有任何问题请私信、评论。