安装mysql

• ubuntu

  • wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-server_5.7.22-1debian9_amd64.deb-bundle.tar

• centos

  • wget https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm

  • sudo rpm -Uvh mysql80-community-release-el7-1.noarch.rpm

  • sudo yum -y install yum-utils

  • yum repolist all | grep mysql

  • sudo yum-config-manager —disable mysql80-community

  • sudo yum-config-manager —enable mysql57-community

  • sudo yum install mysql-community-server

  • sudo systemctl start mysqld.service

  • sudo grep ‘temporary password’ /var/log/mysqld.log

  • mysql -uroot -p

  • ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘NDc2Yzk2M2Mw.’;

基本配置

配置文件位置

• /etc/my.cnf

常用配置:

• 开放远程访问: bind_address=0.0.0.0

• 日志相关:

  • 运行日志: log_error=/var/log/mysqld.log

  • 日志时区: log_timestamps=SYSTEM

  • binlog: log_bin=ushare-bin

  • 慢查询日志

• 密码相关(开发环境):

  • 密码策略: validate_password_policy=0 (0-长度; 1-长度, 数字, 大小写, 特殊字符; 2-1+字典文件)

  • 密码长度: validate_password_length=1

• 字符集: character_set_server=utf8mb4

• 集群相关:

  • 服务器id: server_id

数据库备份/恢复

# no data
mysqldump -uroot -p --databases ushare-user ushare --routines --events --no-data > nodata.sql
# data
mysqldump -uroot -p --databases ushare-user ushare --no-create-db --no-create-info --flush-logs > data.sql
# restore
source nodata.sql

错误解决

1. This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

set global log_bin_trust_function_creators=TRUE;