mac 安装、配置、卸载mysql

mac 安装、配置、卸载mysql

安装

下载mysql dmg包

下载地址：https://downloads.mysql.com/archives/community/

我选择的这个

安装mysql服务

记住安装的数据库的初始密码

启动mysql服务

配置系统环境变量

此时我们在命令行输入mysql -uroot -p命令会提示没有commod not found，我们还需要将mysql加入系统环境变量

• 进入/usr/local/mysql/bin（默认安装路径）,查看此目录下是否有mysql

• 执行vim ~/.bash_profile

添加完成后，按esc，然后输入wq保存。最后在命令行输入source ~/.bash_profile

配置

修改默认密码

set password for 'root'@'localhost' = PASSWORD('root');

例如这里，我mysql的密码修改为root

配置运行远程连接

错误：1130 - Host 127.0.0.1 is not allowed to connect to this MySQL server：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'yourpassword' WITH GRANT OPTION;  /*把命令中的‘yourpassword’换成你的数据库密码*/

修改以后再键入刷新使用权限命令：

FLUSH PRIVILEGES

配置my.cnf文件

mac上mysql默认没有配置文件的，需要自己添加

把/usr/local/mysql/support-files目录下的my-default.cnf复制到桌面，然后改名为my.cnf然后再把这个文件复制到/etc目录

这个文件my.cnf可以加一些我们自己自定义的mysql配置

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[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

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# 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 


default-storage-engine=INNODB
character_set_server=utf8
lower_case_table_names=1
table_open_cache=128
max_connections=2000
max_connect_errors=6000
innodb_file_per_table=1
innodb_buffer_pool_size=1G
max_allowed_packet=64M
transaction_isolation=READ-COMMITTED
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout=1800
innodb_flush_log_at_trx_commit=0
sync_binlog=0
group_concat_max_len=1024000
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
skip-name-resolve



GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION; 

然后重新启动mysql即可

在系统设置->mysql的控制板进行重新启动

这里用命令行重新启动出现错误，不建议

mysql> show VARIABLES like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 67108864   |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)

这里可以发现设置已经生效，max_allowed_packet已经变成64M

卸载

此方法适用于以DMG方式安装的MySQL 5.7

ps -ax | grep mysql
停止并杀死任何 MySQL 进程

brew remove mysql
brew cleanup
sudo rm /usr/local/mysql
sudo rm -rf /usr/local/var/mysql
sudo rm -rf /usr/local/mysql*
sudo rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*

launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

编辑 /etc/hostconfig 删除行 MYSQLCOM=-YES-

rm -rf ~/Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /private/var/db/receipts/*mysql*

重新启动计算机只是为了确保所有 MySQL 进程都被杀死
尝试运行MySQL，无法工作，卸载完成

并且如果是以 下载 DMG 方式安装的MySQL，可以发现设置里的MySQL控制面板已经消失了

允许局域网访问

1、修改my.cnf配置文件

在[mysqld]下添加bind_address=0.0.0.0

最好是先在mac的系统设置中暂停mysql服务，修改之后，然后在启动

2、授权用户

改表

修改mysql的user表，host字段为指定ip即可。如果想任意主机都可以连接，可以使用%

mysql -uroot -p
输入密码
use mysql
select host,user from user;
update user set host = '%' where user = 'root';

新建远程连接用户

# 第一个星号表示数据库名称，第二个星号表示该数据库下的某个表名称。写成两个星号表示所有的数据库都进行授
# admin表示授权admin账号
# “%”表示授权的用户IP可以指定，这里代表任意的IP地址都能访问MySQL数据库
# “123456”表示分配账号对应的密码，这里密码自己替换成你的mysql root帐号密码
grant all on *.* to admin@'%' identified by '123456' with grant option;

#刷新权限
flush privileges;