1. 下载并安装mysql

  1. # 1.下载并解压缩mysql_server服务
  2. mkdir /usr/lib/mysql
  3. sudo tar -xvf mysql-server_5.7.34-1ubuntu18.04_amd64.deb-bundle.tar -C /usr/lib/mysql/
  5. # 2.安装添加的依赖
  6. sudo apt install -y libaio1 libmecab2
  8. # 3.对mysql的deb文件，安装时要注意安装包的顺序
  9. sudo dpkg -i mysql-common_5.7.34-1ubuntu18.04_amd64.deb
  10. sudo dpkg -i libmysqlclient20_5.7.34-1ubuntu18.04_amd64.deb
  11. sudo dpkg -i libmysqlclient-dev_5.7.34-1ubuntu18.04_amd64.deb
  12. sudo dpkg -i libmysqld-dev_5.7.34-1ubuntu18.04_amd64.deb
  13. sudo dpkg -i mysql-community-client_5.7.34-1ubuntu18.04_amd64.deb
  14. sudo dpkg -i mysql-client_5.7.34-1ubuntu18.04_amd64.deb
  16. #设置mysql的root用户，设置密码
  17. sudo dpkg-preconfigure mysql-community-server_5.7.34-1ubuntu18.04_amd64.deb  
  19. sudo dpkg -i mysql-community-server_5.7.34-1ubuntu18.04_amd64.deb
  20. sudo dpkg -i mysql-server_5.7.34-1ubuntu18.04_amd64.deb
  1. ##卸载 MySQL的安装 ##
  2. dpkg --list | grep mysql
  3. sudo apt-get remove mysql-common
  4. sudo apt-get autoremove --purge mysql*
  5. sudo apt-get remove apparmor # 自动卸载mysql的程序
  6. dpkg -l|grep ^rc|awk '{print$2}'|sudo xargs dpkg -P  #清除残留数据
  8. rm -r /var/lib/mysql
  9. rm -r /etc/mysql
  1. ## 常见的dpkg命令 ##
  2. dpkg -i package.deb #安装包
  3. dpkg -r package#删除包
  4. dpkg -P package#删除包(包括配置文件)
  5. dpkg -L package#列出与该包关联的文件
  6. dpkg -l package#显示该包的版本

2.修改配置和添加远程服务

which is not functionally dependent on columns in GROUP BY clause? 解决： 需要在mysqld.conf配置文件中添加 sql_mode (在下面的配置中，已经添加)

  1. ## 1.修改配置文件
  2. vim /etc/mysql/mysql.conf.d/mysqld.conf
  4. ## 2.添加mysql.conf 配置文件
  5. [mysqld]
  6. pid-file        = /var/run/mysqld/mysqld.pid
  7. socket          = /var/run/mysqld/mysqld.sock
  8. datadir         = /var/lib/mysql
  9. log-error       = /var/log/mysql/error.log
  10. port            = 23306
  12. character-set-server = utf8mb4
  13. collation-server     = utf8mb4_general_ci
  15. sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  16. skip_name_resolve
  17. lower_case_table_names=1
  19. # By default we only accept connections from localhost
  20. bind-address  = 0.0.0.0
  21. # Disabling symbolic-links is recommended to prevent assorted security risks
  22. symbolic-links = 0
  24. #
  25. # * Query Cache Configuration
  26. query_cache_limit       = 1M
  27. query_cache_size        = 16M
  29. #
  30. # * log_bin
  31. expire_logs_days        = 30
  32. max_binlog_size         = 256M
  35. group_concat_max_len=10240000
  36. max_allowed_packet=1G
  37. max_connections=8000
  1. ## 4.登录数据库，修改远程访问的权限
  2. mysql -u root -p
  3. use mysql;
  4. grant all on *.* to 'root'@'%' identified by 'root用户的密码' with grant option;
  5. flush privileges;
  1. ### MySQL5.7跟8.0的加密方式不一样 ###
  2. mysql> ALTER USER 'test'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; #修改加密规则 
  3. mysql> ALTER USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY 'password';  #更新一下用户的密码  
  4. mysql> FLUSH PRIVILEGES; #刷新权限
  1. ## MySQL的操作
  2. service mysql status
  3. service mysql restart
  4. service mysql start | stop

3.MySQL使用小技巧

  1. # 重启mysql服务
  2. service mysqld restart
  4. # 停止mysql服务
  5. service mysqld stop
  7. # 启动mysql服务
  8. service mysqld start
  10. # 登录本机mysql数据库
  11. mysql -uroot -p
  13. # 登录远程mysql数据库
  14. mysql -h主机地址 -u用户名 -p用户密码
  16. # 注意在mysql命令行中使用命令都需要以分号结尾";"之后再回车，命令才会执行
  17. # 查看mysql运行状态
  18. service mysqld status 
  20. # 查看mysql的运行使用的进程
  21. ps -e |grep mysql