参考来源：

https://blog.csdn.net/liuxw1/article/details/81434005 https://blog.csdn.net/cheng649090216/article/details/79246333 https://dev.mysql.com/downloads/mysql/5.7.html https://www.jianshu.com/p/0f8213da9f20

mysql 完全卸载步骤-mac系统

  1. brew remove mysql
  2. brew cleanup
  3. launchctl unload -w ~/Library/LaunchAgents/com.mysql.mysqld.plist
  4. rm ~/Library/LaunchAgents/com.mysql.mysqld.plist
  5. sudo rm -rf /usr/local/var/mysql
  6. sudo rm -rf /usr/local/mysql*
  7. sudo rm -rf /Library/StartupItems/MySQLCOM
  8. sudo rm -rf /Library/PreferencePanes/My*
  9. sudo rm -rf ~/Library/PreferencePanes/My*
  10. sudo rm -rf /Library/Receipts/mysql*
  11. sudo rm -rf /Library/Receipts/MySQL*
  12. sudo rm -rf /var/db/receipts/com.mysql.*

mysql centos系统安装

cat /etc/redhat-releaseCentOS 系统版本：Linux release 7.9.2009 (Core)

mysql关于mysqld_safe的总结

http://blog.itpub.net/30126024/viewspace-2221483/

  1. wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
  2. yum localinstall -y mysql57-community-release-el7-11.noarch.rpm
  4. yum repolist enabled | grep "mysql.*-community.*"
  6. # 我这次按照查询的版本号：5.7.36-1.el7
  7. yum install -y mysql-community-server
  9. systemctl status mysqld（service mysqld status）
  10. systemctl start mysqld
  11. systemctl restart mysqld
  12. 获取密码：grep 'temporary password' /var/log/mysqld.log
  14. mysql -uroot -p
  16. set global validate_password_policy=0;
  17. set global validate_password_length=4;
  18. alter user 'root'@'localhost' identified by 'root';
  20. use mysql
  21. update user set host='%' where user ='root';
  23. exit
  24. systemctl restart mysqld
  25. systemctl enable mysqld
  26. systemctl daemon-reload

配置文件调整vi /etc/my.cnf

  1. [mysqld]
  2. max_connections=200
  3. max_connect_errors=10
  4. character-set-server=utf8mb4
  5. default-storage-engine=INNODB
  7. [mysql]
  8. default-character-set=utf8mb4
  10. [mysql.server]
  11. default-character-set=utf8mb4
  13. [mysql_safe]
  14. default-character-set=utf8mb4
  16. [client]
  17. port=3306
  18. default-character-set=utf8mb4

mysql mac系统brew安装步骤

brew 安装 mysql@5.7

  1. brew search mysql
  2. brew install mysql@5.7 
  3. ln -sfv /usr/local/opt/mysql@5.7/*.plist ~/Library/LaunchAgents
  5. my.cnf 配置文件找到写入内容（可忽略）
  6. mysql.server start

接下来设置mysql密码

  1. mysql -uroot
  2. show databases;
  4. use mysql
  5. update user set authentication_string = password('root') where User='root';

刷新权限

  1. flush privileges;

重启mysql服务

  1. mysql.server stop
  2. mysql.server start --skip-grant-tables （安全模式启动）

mysql my.cnf 加载顺序

  1. /etc/my.cnf
  2. /etc/mysql/my.cnf
  3. /usr/local/etc/my.cnf
  4. ~/.my.cnf

my.cnf mac配置文件内容

  1. # my.cnf mac配置文件内容
  2. # Example MySQL config file for medium systems.
  3. #
  4. # This is for a system with little memory (32M - 64M) where MySQL plays
  5. # an important part, or systems up to 128M where MySQL is used together with
  6. # other programs (such as a web server)
  7. #
  8. # MySQL programs look for option files in a set of
  9. # locations which depend on the deployment platform.
  10. # You can copy this option file to one of those
  11. # locations. For information about these locations, see:
  12. # http://dev.mysql.com/doc/mysql/en/option-files.html
  13. #
  14. # In this file, you can use all long options that a program supports.
  15. # If you want to know which options a program supports, run the program
  16. # with the "--help" option.
  17. # The following options will be passed to all MySQL clients
  18. [client]
  19. default-character-set=utf8
  20. #password = your_password
  21. port = 3306
  22. socket = /tmp/mysql.sock
  23. # Here follows entries for some specific programs
  24. # The MySQL server
  25. [mysqld]
  26. character-set-server=utf8
  27. init_connect='SET NAMES utf8
  28. port = 3306
  29. socket = /tmp/mysql.sock
  30. skip-external-locking
  31. key_buffer_size = 16M
  32. max_allowed_packet = 1M
  33. table_open_cache = 64
  34. sort_buffer_size = 512K
  35. net_buffer_length = 8K
  36. read_buffer_size = 256K
  37. read_rnd_buffer_size = 512K
  38. myisam_sort_buffer_size = 8M
  39. character-set-server=utf8
  40. secure_file_priv=''
  41. init_connect='SET NAMES utf8'
  42. # Don't listen on a TCP/IP port at all. This can be a security enhancement,
  43. # if all processes that need to connect to mysqld run on the same host.
  44. # All interaction with mysqld must be made via Unix sockets or named pipes.
  45. # Note that using this option without enabling named pipes on Windows
  46. # (via the "enable-named-pipe" option) will render mysqld useless!
  47. #
  48. #skip-networking
  51. # Replication Master Server (default)
  52. # binary logging is required for replication
  53. log-bin=mysql-bin
  56. # binary logging format - mixed recommended
  57. binlog_format=mixed
  60. # required unique id between 1 and 2^32 - 1
  61. # defaults to 1 if master-host is not set
  62. # but will not function as a master if omitted
  63. server-id = 1
  66. # Replication Slave (comment out master section to use this)
  67. #
  68. # To configure this host as a replication slave, you can choose between
  69. # two methods :
  70. #
  71. # 1) Use the CHANGE MASTER TO command (fully described in our manual) -
  72. # the syntax is:
  73. #
  74. # CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
  75. # MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
  76. #
  77. # where you replace <host>, <user>, <password> by quoted strings and
  78. # <port> by the master's port number (3306 by default).
  79. #
  80. # Example:
  81. #
  82. # CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
  83. # MASTER_USER='joe', MASTER_PASSWORD='secret';
  84. #
  85. # OR
  86. #
  87. # 2) Set the variables below. However, in case you choose this method, then
  88. # start replication for the first time (even unsuccessfully, for example
  89. # if you mistyped the password in master-password and the slave fails to
  90. # connect), the slave will create a master.info file, and any later
  91. # change in this file to the variables' values below will be ignored and
  92. # overridden by the content of the master.info file, unless you shutdown
  93. # the slave server, delete master.info and restart the slaver server.
  94. # For that reason, you may want to leave the lines below untouched
  95. # (commented) and instead use CHANGE MASTER TO (see above)
  96. #
  97. # required unique id between 2 and 2^32 - 1
  98. # (and different from the master)
  99. # defaults to 2 if master-host is set
  100. # but will not function as a slave if omitted
  101. #server-id = 2
  102. #
  103. # The replication master for this slave - required
  104. #master-host = <hostname>
  105. #
  106. # The username the slave will use for authentication when connecting
  107. # to the master - required
  108. #master-user = <username>
  109. #
  110. # The password the slave will authenticate with when connecting to
  111. # the master - required
  112. #master-password = <password>
  113. #
  114. # The port the master is listening on.
  115. # optional - defaults to 3306
  116. #master-port = <port>
  117. #
  118. # binary logging - not required for slaves, but recommended
  119. #log-bin=mysql-bin
  122. # Uncomment the following if you are using InnoDB tables
  123. #innodb_data_home_dir = /usr/local/mysql/data
  124. #innodb_data_file_path = ibdata1:10M:autoextend
  125. #innodb_log_group_home_dir = /usr/local/mysql/data
  126. # You can set .._buffer_pool_size up to 50 - 80 %
  127. # of RAM but beware of setting memory usage too high
  128. #innodb_buffer_pool_size = 16M
  129. #innodb_additional_mem_pool_size = 2M
  130. # Set .._log_file_size to 25 % of buffer pool size
  131. #innodb_log_file_size = 5M
  132. #innodb_log_buffer_size = 8M
  133. #innodb_flush_log_at_trx_commit = 1
  134. #innodb_lock_wait_timeout = 50
  137. [mysqldump]
  138. quick
  139. max_allowed_packet = 16M
  142. [mysql]
  143. no-auto-rehash
  144. # Remove the next comment character if you are not familiar with SQL
  145. #safe-updates
  146. default-character-set=utf8
  149. [myisamchk]
  150. key_buffer_size = 20M
  151. sort_buffer_size = 20M
  152. read_buffer = 2M
  153. write_buffer = 2M
  156. [mysqlhotcopy]
  157. interactive-timeout

Windows安装Mysql

  1. • path bin环境变量配置（略过）
  2. a. mysqld --initialize --console （执行完毕之后会生成初始密码）
  3. b. mysqld --install [服务名]（服务名可以不加默认为mysql）
  4. c. mysqld --install mysql --defaults-file="xxxx/my.ini"
  5. d. sc delete mysql [删除服务]
  6. e. net start mysql [启动MySQL的服务]

my.ini Windows配置文件内容

  1. # my.ini windows配置文件内容
  3. [mysqld]
  4. # 设置3306端口 
  5. port=3306
  6. # 设置mysql的安装目录
  7. basedir=C:\Program Files\MySQL
  8. # 设置mysql数据库的数据的存放目录
  9. datadir=C:\Program Files\MySQL\Data
  10. # 允许最大连接数
  11. max_connections=200
  12. # 允许连接失败的次数。
  13. max_connect_errors=10
  14. # 服务端使用的字符集默认为utf8mb4
  15. character-set-server=utf8mb4
  16. # 创建新表时将使用的默认存储引擎
  17. default-storage-engine=INNODB
  18. # 默认使用“mysql_native_password”插件认证
  19. #mysql_native_password
  20. default_authentication_plugin=mysql_native_password
  21. # 选用sql的严格模式
  22. # sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
  24. [mysql]
  25. # 设置mysql客户端默认字符集
  26. default-character-set=utf8mb4
  28. [mysql.server]
  29. # 设置mysql客户端默认字符集
  30. default-character-set=utf8mb4
  32. [mysql_safe]
  33. default-character-set=utf8mb4
  35. [client]
  36. # 设置mysql客户端连接服务端时默认使用的端口
  37. port=3306
  38. default-character-set=utf8mb4

MySql 命令

更新域属性，’%’表示允许外部访问;

  1. update user set host='%' where user ='root';

执行以上语句之后再执行(刷新配置)

  1. FLUSH PRIVILEGES;
  2. GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION;

创建用户并给予root权限 nacos（你的用户名）

  1. CREATE USER 'nacos'@'%' IDENTIFIED BY 'nacos';
  2. GRANT ALL PRIVILEGES ON *.* TO 'nacos'@'%'WITH GRANT OPTION;

设置密码长度、安全型

  1. set global validate_password_policy=0;
  2. set global validate_password_length=4;

版本号查询

  1. select version();

提供什么存储引擎

  1. show engines;

当前默认的存储引擎

  1. show variables like '%storage_engine%';

查询数据库存储

  1. select table_schema                            as '数据库',
  2.        table_name                              as '表名',
  3.        table_rows                              as '记录数',
  4.        truncate(data_length / 1024 / 1024, 2)  as '数据容量(MB)',
  5.        truncate(index_length / 1024 / 1024, 2) as '索引容量(MB)'
  6. from information_schema.tables
  7. where table_schema = 'xxxxxxxxxxxtable'
  8. order by data_length desc, index_length desc
  9. limit 0, 10

Mac mysql 设置可远程连接

怎么知道mysql的配置文件在哪里呢？

  1. mysql --help
  4. Default options are read from the following files in the given order:
  5. /etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

修改mysql配置，由于是通过brew安装的mysql，配置文件在：/usr/local/etc/my.cnf；
找到：bind-address = 127.0.0.1
修改为：bind-address = 0.0.0.0 或用#注释掉
然后mysql重启：

  1. mysql.server restart