一、mysql下载安装 (mac)
- 1.1 下载mysql
点击进入mysql下载页, 下载最新版本MySQL Community Server
-
1.2 安装
安装过程比较简单,中途会弹出root用户初始化的登陆密码,如图:
mysql默认安装目录为:/usr/local, mysql安装会添加如下两个文件夹
```
mysql
mysql-8.0.1-dmr-macos10.12-x86_64
```
二、mysql基本操作
- 2.1 mysql服务器未启动-登陆
mysql安装完毕后,mysql服务器默认关闭,连接mysql会报如下错误:
```
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
```
-
2.2 启动mysql服务器
-
2.2.1 命令行启、停、重启mysql服务器
sudo /usr/local/mysql/support-files/mysql.server start sudo /usr/local/mysql/support-files/mysql.server stop sudo /usr/local/mysql/support-files/mysql.server restart 或者修改用户环境变量文件 ~/.bash_profile,在文件末尾添加: # mysql alias mysqlstart='sudo /usr/local/mysql/support-files/mysql.server start' alias mysqlstop='sudo /usr/local/mysql/support-files/mysql.server stop' alias mysqlrestart='sudo /usr/local/mysql/support-files/mysql.server restart'
-
2.2.2 通过操作界面启、停mysql服务器
-
-
2.3 登陆mysql&查看mysql版本
登陆mysql:mysql -u[username] -p[password]
查看mysql版本:select version();
leiyudeMacBook-Air-3:~ Jerry$ mysql -uroot -p123 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.1-dmr MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.1-dmr | +-----------+ 1 row in set (0.00 sec)
-
2.4 修改密码
安装mysql过程中曾经弹出了mysql root用户密码,可修改该密码-
2.4.1 通过mysqladmin修改:mysqladmin -uroot -p password [new password]
leiyudeMacBook-Air-3:~ Jerry$ mysqladmin -uroot -p password 123 Enter password: mysqladmin: [Warning] Using a password on the command line interface can be insecure. Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
-
2.4.2 修改mysql用户表
update mysql.user set authentication_string=password('xxx') where user='root'; flush privileges; #刷新内存中权限
flush privileges说明:flush privileges命令作用本质是将当前user和privilege表中的用户信息、权限设置从mysql库中提取到内存里,当mysql启动时,所有的授权表内容被读取进存储器并生效。使用grant、revoke或set password对授权表进行修改会立即被服务器感知到并生效,但如果使用insert、update、delete等人工修改授权表,需要执行flush privileges让服务器重新加载用户授权信息。
-
-
2.5 忘记mysql密码
mysql启动时,可通过添加skip-grant-tables启动参数免密进入mysql服务器。添加该参数后将不使用mysql数据库里的信息来进行访问控制,将允许任意用户修改任意数据库,有较大风险。同时,这为偶尔忘记mysql密码时修改密码提供了便利。启动示例如下:
```
sudo /usr/local/mysql/support-files/mysql.server start --skip-grant-tables
```
mysql启动后,可免密进入mysql服务器,此时可修改并保存用户密码。
```
update mysql.user set authentication_string=password('xxx') where user='root';
```
退出并正常重启mysql服务器,可能会遇到1820错误,解决方式如下:
```
错误:
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
二次修改密码:
set password=password('xxx');
```
- 2.6 卸载mysql
mac下mysql的安装包中有安装文件,但是没有卸载文件,需要手动删除mysql相关文件。如果删除不彻底,可能会导致如下问题:
```
ERROR 1006 (HY000): Can't create database 'test' (errno: 71732400)
```
mysql彻底卸载过程:1.关闭mysql服务器;2.终端执行如下命令删除mysql相关文件;3.重启电脑
```
sudo rm /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
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使用测试
-
3.1 查看并创建数据库
mysql> show databases; mysql> create database test; mysql> use test;
-
3.2 创建单表
CREATE TABLE IF NOT EXISTS user( id INT UNSIGNED AUTO_INCREMENT, name VARCHAR(100) NOT NULL, age INT(11) NOT NULL, PRIMARY KEY ( id ) );
-
3.3 添加纪录
insert into user(name, age) values('Jerry', 28), ('Tonglin', 25);
-
3.4 数据导出:mysqldump
mysqldump命令用于数据导出,也位于/usr/local/mysql目录下。为真实mysqldump命令取别名
```
alias mysqldump='/usr/local/mysql/bin/mysqldump'
```
在终端导出数据库中数据,导出数据保存到当前目录。
```
mysqldump -uroot -pxxx test user > user.sql
结果:
leiyudeMacBook-Air-3:~ Jerry$ ls user.*
user.sql
```
- 3.5 数据导入
在已有的数据库中导入数据,首先进入到mysql服务器,进入想要导入数据的数据库。有如下两种方式可到入数据
```
1.mysql服务器上执行
mysql> source /Users/Jerry/user.sql;
2.终端执行
leiyudeMacBook-Air-3:~ Jerry$ mysql -uroot -p test < user.sql
结果:
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| user |
+-----------------+
```
-
3.6 查看表结构
插入数据前,先查看表结构:
mysql> desc user; --- +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(100) | NO | | NULL | | | age | int(11) | NO | | NULL | | +-------+------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
-
3.7 查询数据库中所有表名及表描述
select TABLE_NAME, TABLE_COMMENT from information_schema.tables where table_schema='数据库名'