Skip to main content

Mac Mariadb|Mysql8 安装|配置|备份|还原

homebrew 安装mysql​

安装​

# 搜索mysql
brew search mysql
# 查看当前mysql 版本信息
brew info mysql
# 安装mysql
brew install mysql

配置​

# 启动mysql
brew services start mysql
# 完成mysql安全配置
mysql_secure_installation

####################### 安全配置过程中 提示内容和配置项 #######################
Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?
## 1 是否设置密码安全等级：y
Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file
## 2 输入密码安全等级：0
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Please set the password for root here.
## 3 输入密码：123456abcx
New password: 

Re-enter new password: 

## 4 提示当前密码安全评分，确认是否使用改密码：y
Estimated strength of the password: 50 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

## 5 是否删除匿名用户：y
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

## 6 是否禁用远程root登录：y
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

## 7 是否删除测试数据库：y
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

## 8 是否重新加载权限表：y
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done! 
####################### 安全配置过程中 提示内容和配置项 #######################

查看数据库运行状态​

# 通过进程查看 sql相关的进程信息
ps aux |grep -e 'USER' -e 'sql'
# 查看所有brew services 清单和状态
brew services list
# 通过brew services 查看mysql详细状态
brew services info mysql
# 通过mysqld 产看配置文件位置
mysqld --help --verbose | grep cnf
# 通过mysql 查看配置文件位置
mysql --help |grep cnf
# 通过mysql语句 当前生效的配置变量
SHOW VARIABLES LIKE 'slow_query%'

Homebrew安装Mariadb​

检查&安装xcode​

xcode-select --install

检查&安装Homebrew​

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"

对homebrew进行体检​

brew doctor

更新homebrew​

brew update

查看homebrew中mariadb信息​

brew info mariadb

安装mariadb​

brew install mariadb

初始化mariadb数据库​

#初始化 安装数据库 命令：
$ mysql_install_db
$ mariadb-install-db # mariadb新版11.4 改用该命令

# 如果提示: mysql.user table already exists!
# 可以手动删除
cd /opt/homebrew/var && rm -rf mysql

# 安装的结果 注意内容：默认创建的账户信息：root 和 zhoujh
Two all-privilege accounts were created.
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is zhoujh@localhost, it has no password either, but
you need to be the system 'zhoujh' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo

启动mariadb​

# 启动mariadb
brew services start mariadb
# 停止mariadb
brew services stop mariadb

初始化配置数据库用户​

# 数据库用户安全配置命令
mysql_secure_installation
mariadb-secure-installation  # mariadb新版11.4 改用该命令
# 注意：如果是配置root账户 需要sudo
sudo mysql_secure_installation
sudo mariadb-secure-installation # mariadb新版11.4 改用该命令
# 1 数据库用户密码 初始密码为空，直接按回车
Enter current password for root (enter for none): 
OK, successfully used password, moving on
# 2 是否允许系统用户直接登录 新增功能，输入“n”
Enable unix_socket authentication? [Y/n] n
 ... skipping.
# 3 修改root用户密码 y
Change the root password? [Y/n] y
New password:									# 输入新密码
Re-enter new password:				# 再次输入新密码
Password updated successfully! 
# 4 删除anonymous用户 y
Remove anonymous users? [Y/n] y
 ... Success!
# 5 是否禁止远程登录 y 注意 如果是需要通过root远程登录选择 n
Disallow root login remotely? [Y/n] y
 ... Success!
# 6 删除测试数据库 y
Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!
# 7 重新加载权限表 y
Reload privilege tables now? [Y/n] y
 ... Success!

至此完成安装​

配置｜调优｜运维​

通过sequel ace 数据库管理软件 添加新用户​

  1. 配置sequel ace 访问mariadb
  2. 连接上mariadb
  3. cmd+u快捷键 打开数据库用户管理
  4. 添加新用户和密码
  5. 配置新用户为本的还是远程
  6. 配置新用户权限
  7. 完成添加

备份和还原​

带压缩的备份和还原数据库​

# 备份 k数据库 保存到 k-09-15.sql.gz
mysqldump -uroot -p123456 k | gzip > k-09-15.sql.gz
# 还原 k-09-15.sql.gz 到k数据库
gunzip < /volume1/download/k-09-15.sql.gz | mysql -uroot -p k

备份数据库​

# 备份 sdb数据库 保存到 sdb.sql
mysqldump  -uroot -p123456 --database sdb > sdb.sql;
# 还原 sdb数据库 来自sdb.sql备份文件
source sdb.sql

# 导出数据库：   
mysqldump -u 用户名 -p 数据库名 > 导出的文件名
# 导出表：    
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名；
# 导出数据库结构： 
mysqldump -u user_name -p -d –add-drop-table database_name > outfile_name.sql；
# 例子1 导出整个数据库
mysqldump -uroot -p123456 k_qa > /volume1/soft/DB\ copy/k_qa_1027_05_13.sql
# 例子2 导出一个表
mysqldump -u user_name -p database_name table_name > outfile_name.sql
#例子3 导出一个数据库结构
#	-d = 没有数据 
#	–add-drop-table = 在每个create语句之前增加一个drop table
mysqldump -u user_name -p -d –add-drop-table database_name > outfile_name.sql

还原数据库​

# 1.创建数据库（mysql 命令）：
create database <数据库名>;
# 2.导入数据到数据库：
mysql -u用户名 -p密码 数据库 <文件名>；
# 如果导入过程出错导致导入中断，可以加 -f参数 忽略错误 继续导入：  
mysql -u用户名 -p密码 -f 数据库 < 文件名>；
# 例子 
	# 1 创建k_qa 数据库
	create database k_qa;
	# 2 还原数据到k_qa
	mysql -uroot -p123456 k_qa < /volume1/soft/DB\ copy/k_qa_2017-05-12.sql

SequelPro导入出现错误: Unknown collation: 'utf8mb4_0900_ai_ci'​

# 说明本地的mariadb不支持，只需要将待导入的sql文件用sed批量替换即可
sed -i '' 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' star-2024-7-8-17-57-59.sql
# 这个命令的解释如下：
## sed：流编辑器，用于对输入文本进行基于模式的编辑。
## -i ''：表示直接修改输入文件，而不是将修改后的内容输出到标准输出。
## 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g'：这是一个 sed 表达式，用于查找并替换模式。s 表示替换（substitute），/ 是分隔符，utf8mb4_0900_ai_ci 是要查找的模式，utf8mb4_general_ci 是要替换成的模式，g 表示全局替换（global），即替换每一行中的所有匹配项。
## my_database.sql：要处理的 SQL 文件名。

参考：相关命令​

建立数据库连接​

# 命令格式（连本机可省主机地址）：
mysql -h主机地址 -u用户名 －p用户密码
# 本机例子：
mysql -uroot -p123456
# 远程例子：
mysql -h192.168.0.200 -uroot -p123456
# 退出
exit;

修改密码​

# 命令格式：
mysqladmin -u用户名 －p旧密码 password 新密码
# 例子1 (root 初始密码为空，可省略“-p旧密码”)：
mysqladmin -uroot -password 1234abcd 
# 例子2：
mysqladmin -uroot -p1234abcd password 123abc

增加用户​

# 命令格式（mysql 命令）：
grant select,insert,update,delete on 数据库.* to 用户名@登录主机 identified by “密码”;
# 例子1:新增用户test1 密码为abc，可在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限：
grant select,insert,update,delete on *.* to [email=test1@”%]test1@”%[/email]” Identified by “abc”;
# 新增用户test2 密码为abc,只可在localhost上登录，并可以对数据库mydb进行查询、插入、修改、删除的操作：
grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “abc”;

创建数据库​

# 命令格式（mysql 命令）：
create database <数据库名>;
# 例子1 建立一个名为xhkdb的数据库:
mysql> create database xhkdb;
# 创建数据库并分配用户,依次执行3个命令完成数据库创建:
CREATE DATABASE 数据库名;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON 数据库名.* TO 数据库名@localhost IDENTIFIED BY '密码';
SET PASSWORD FOR '数据库名'@'localhost' = OLD_PASSWORD('密码’);

显示数据库​

# 命令格式（mysql 命令）：
show databases;

删除数据库​

# 命令格式（mysql 命令）：
drop database <数据库名>;
# 例子 删除一个名为s的数据库:
mysql> drop database s;
# 例子 删除一个名为s的数据库（不确定是否存在）:
mysql> drop database if exists s;

连接数据库​

# 命令格式（mysql 命令）：
use <数据库名>;
# 例子：
mysql> use s;

创建表​

# 命令格式（mysql 命令）：
create table <表名>(<字段名1><类型1>[,…<字段名n><类型n>]);
# 例子：建立一个名为MyClass的表，
#  | 字段名  | 数字类型  | 数据宽度   | 是否为空  | 是否主键     | 自动增加         | 默认值 |
#  | ------ | -------- | -------- | -------- | ----------- | -------------- | ------ |
#  | id     | int      | 4        | 否       | primary key | auto_increment |        |
#  | name   | char     | 20       | 否       |             |                |        |
#  | sex    | int      | 4        | 否       |             |                | 0      |
#  | degree | double   | 16       | 是       |             |                |        |
mysql> create table MyClass(
  > id int(4) not null primary key auto_increment,
  > name char(20) not null,
  > sex int(4) not null default '0',
  > degree double(16,2));

删除表​

# 命令格式（mysql 命令）：
drop table <表名>;
# 例子：
mysql> drop table MyClass;

插入数据​

# 命令格式（mysql 命令）：
insert into <表名> [(<字段名1>[,…<字段名n>])] values (值1)[,…(值n)];
# 例子：往表 MyClass中插入二条记录, 这二条记录表示：编号为1的名为Tom的成绩为96.45, 编号为2 的名为Joan 的成绩为82.99， 编号为3 的名为Wang 的成绩为96.5。
mysql> insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);
# 注意：insert into每次只能向表中插入一条记录。

查询数据​

# 命令格式（mysql 命令）：
select <字段1，字段2，...> from < 表名 > where < 表达式 >；
# 例子： 查看表 MyClass 中所有数据
mysql> select * from MyClass;
# 例子： 查看表 MyClass 中前2行数据
mysql> select * from MyClass order by id limit 0,2;

删除数据​

# 命令格式（mysql 命令）：
delete from 表名 where 表达式****；
# 例如：删除表 MyClass中编号为1 的记录
mysql> delete from MyClass where id=1;

修改数据​

# 命令格式（mysql 命令）：
update 表名 set 字段=新值,… where 条件****；
# 例子：
mysql> update MyClass set name='Mary' where id=1;

增加字段​

# 命令格式（mysql 命令）：
alter table 表名 add 字段 类型 其他；
# 例子：在表MyClass中添加了一个字段passtest，类型为int(4)，默认值为0
mysql> alter table MyClass add passtest int(4) default '0'

索引编辑​

# 加索引
mysql> alter table 表名 add index 索引名 (字段名1[，字段名2 …]);
	#	例子： 
	mysql> alter table employee add index emp_name (name);
#	加主关键字的索引
mysql> alter table 表名 add primary key (字段名);
	# 例子： 
	mysql> alter table employee add primary key(id);
#	加唯一限制条件的索引
mysql> alter table 表名 add unique 索引名 (字段名);
	# 例子： 
	mysql> alter table employee add unique emp_name2(cardnumber);
# 删除某个索引
mysql> alter table 表名 drop index 索引名;
	# 例子： 
	mysql>alter table employee drop index emp_name;
# 增加字段：
mysql> ALTER TABLE table_name ADD field_name field_type;
# 修改原字段名称及类型：
mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
#	删除字段：
MySQL ALTER TABLE table_name DROP field_name;

修改表名​

# 命令格式（mysql 命令）：
rename table 原表名 to 新表名****；
# 例子：在表MyClass名字更改为YouClass
mysql> rename table MyClass to YouClass;

参考：Mariadb安装​

Installing MariaDB on Mac OS X with Homebrew

Back in 2016, we blogged about deploying MariaDB 10.1.16 on Mac OS X with Homebrew. Homebrew now includes MariaDB 10.4, 10.3, 10.2, and 10.1 for macOS. We’ve refreshed this blog to reflect the latest technology. If you need the old blog, you can find it here.

1. Install Xcode​

Run xcode-select --install.

$ xcode-select --install
xcode-select: note: install requested for command line developer tools

2. Install Homebrew​

Run /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)".

$  /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"
==> This script will install:
/usr/local/bin/brew
/usr/local/share/doc/homebrew
/usr/local/share/man/man1/brew.1
/usr/local/share/zsh/site-functions/\_brew
/usr/local/etc/bash\_completion.d/brew
/usr/local/Homebrew
==> The following new directories will be created:
/usr/local/sbin
/usr/local/Caskroom
 
Press RETURN to continue or any other key to abort
Password:
==> /usr/bin/sudo /bin/mkdir -p /usr/local/sbin /usr/local/Caskroom
==> /usr/bin/sudo /bin/chmod g+rwx /usr/local/sbin /usr/local/Caskroom
==> /usr/bin/sudo /usr/sbin/chown rob /usr/local/sbin /usr/local/Caskroom
==> /usr/bin/sudo /usr/bin/chgrp admin /usr/local/sbin /usr/local/Caskroom
==> Downloading and installing Homebrew...
remote: Enumerating objects: 5822, done.
remote: Counting objects: 100% (5822/5822), done.
remote: Compressing objects: 100% (43/43), done.
remote: Total 24553 (delta 5779), reused 5821 (delta 5779), pack-reused 18731
Receiving objects: 100% (24553/24553), 7.33 MiB | 1.09 MiB/s, done.
Resolving deltas: 100% (18413/18413), completed with 1257 local objects.
...
HEAD is now at 67d1bc6fb Merge pull request #7615 from Bo98/test-dep-satisfied
==> Downloading https://homebrew.bintray.com/bottles-portable-ruby/portable-ruby-2.6.3.mavericks.bottle.tar.gz
######################################################################## 100.0%
==> Pouring portable-ruby-2.6.3.mavericks.bottle.tar.gz
Updated 1 tap (homebrew/core).
==> New Formulae
...
==> Renamed Formulae
...
==> Deleted Formulae
...
==> Installation successful!
 
==> Homebrew has enabled anonymous aggregate formulae and cask analytics.
Read the analytics documentation (and how to opt-out) here:
  https://docs.brew.sh/Analytics
No analytics data has been sent yet (or will be during this \`install\` run).
 
==> Homebrew is run entirely by unpaid volunteers. Please consider donating:
  https://github.com/Homebrew/brew#donations
 
==> Next steps:
- Run \`brew help\` to get started
- Further documentation: 
    https://docs.brew.sh


3. Check Homebrew​

Run brew doctor. Follow on-screen instructions to fix warnings if necessary.

$ brew doctor
Please note that these warnings are just used to help the Homebrew maintainers with debugging if you file an issue. If everything you use Homebrew for is working fine: please don't worry or file an issue; just ignore this. Thanks!
 
Warning: You have unlinked kegs in your Cellar.
Leaving kegs unlinked can lead to build-trouble and cause brews that depend on those kegs to fail to run properly once built. Run \`brew link\` on these:
  gettext
  git
 
Warning: Some installed formulae are missing dependencies.
You should \`brew install\` the missing dependencies:
  brew install openssl@1.1
 
Run \`brew missing\` for more details.
$ brew link gettext git
Linking /usr/local/Cellar/gettext/0.19.8.1... 187 symlinks created
Linking /usr/local/Cellar/git/2.19.1... 
Error: Could not symlink bin/git
Target /usr/local/bin/git
already exists. You may want to remove it:
  rm '/usr/local/bin/git'
 
To force the link and overwrite all conflicting files:
  brew link --overwrite git
 
To list all files that would be deleted:
  brew link --overwrite --dry-run git
 
...


4. Update Homebrew​

Run brew update.

$ brew update
Already up-to-date.

5. Verify MariaDB version in Homebrew repo​

Run brew info mariadb.

$ brew info mariadb
mariadb: stable 10.4.13 (bottled)
Drop-in replacement for MySQL
https://mariadb.org/
Conflicts with:
mariadb-connector-c (because both install plugins)
mysql (because mariadb, mysql, and percona install the same binaries)
mytop (because both install \`mytop\` binaries)
percona-server (because mariadb, mysql, and percona install the same binaries)
/usr/local/Cellar/mariadb/10.2.14 (641 files, 168.6MB)
Poured from bottle on 2018-04-30 at 11:34:15
/usr/local/Cellar/mariadb/10.3.10 (652 files, 173.3MB) \*
Built from source on 2018-10-12 at 07:16:37
From: https://github.com/Homebrew/homebrew-core/blob/master/Formula/mariadb.rb
==> Dependencies
Build: cmake ✘, pkg-config ✘
Required: groonga ✘, openssl@1.1 ✔
==> Caveats
A "/etc/my.cnf" from another install may interfere with a Homebrew-built
server starting up correctly.

MySQL is configured to only allow connections from localhost by default

To have launchd start mariadb now and restart at login:
brew services start mariadb
Or, if you don't want/need a background service you can just run:
mysql.server start
==> Analytics
install: 15,161 (30 days), 36,985 (90 days), 172,584 (365 days)
install-on-request: 14,780 (30 days), 36,286 (90 days), 168,365 (365 days)
build-error: 0 (30 days)

6. Install MariaDB​

Run brew install mariadb. Follow on-screen instructions to upgrade if necessary to upgrade a previously installed version.

$ brew install mariadb
Updating Homebrew...
==> Auto-updated Homebrew!
Updated 1 tap (homebrew/core).
==> Updated Formulae
fonttools                                                           timidity
 
==> Downloading https://homebrew.bintray.com/bottles/mecab-0.996.mojave.bottle.3.tar.gz
==> Downloading from https://akamai.bintray.com/ef/\[...\]?\_\_gda\_\_=exp=1590016
######################################################################## 100.0%
==> Downloading https://homebrew.bintray.com/bottles/mecab-ipadic-2.7.0-20070801.mojave.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/30/\[...\]?\_\_gda\_\_=exp=1590016
######################################################################## 100.0%
==> Downloading https://homebrew.bintray.com/bottles/msgpack-3.2.1.mojave.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/3b/\[...\]?\_\_gda\_\_=exp=1590016
######################################################################## 100.0%
==> Downloading https://homebrew.bintray.com/bottles/pcre-8.44.mojave.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/ed/\[...\]?\_\_gda\_\_=exp=1590016
######################################################################## 100.0%
==> Downloading https://homebrew.bintray.com/bottles/groonga-10.0.2.mojave.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/df/\[...\]?\_\_gda\_\_=exp=1590016
######################################################################## 100.0%
==> Downloading https://homebrew.bintray.com/bottles/mariadb-10.4.13.mojave.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/e4/\[...\]?\_\_gda\_\_=exp=1590016
######################################################################## 100.0%
Error: mariadb 10.3.10 is already installed
To upgrade to 10.4.13, run \`brew upgrade mariadb\`.
 
$ brew upgrade mariadb
==> Upgrading 1 outdated package:
mariadb 10.3.10 -> 10.4.13
==> Upgrading mariadb 10.3.10 -> 10.4.13 
==> Downloading https://homebrew.bintray.com/bottles/cmake-3.17.2.mojave.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/ed/\[...\]?\_\_gda\_\_=exp=1590016
######################################################################## 100.0%
==> Downloading https://homebrew.bintray.com/bottles/pkg-config-0.29.2\_3.mojave.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/0d/\[...\]?\_\_gda\_\_=exp=1590016
######################################################################## 100.0%
==> Downloading https://homebrew.bintray.com/bottles/mecab-0.996.mojave.bottle.3.tar.gz
Already downloaded: /Users/rob/Library/Caches/Homebrew/downloads/\[...\]--mecab-0.996.mojave.bottle.3.tar.gz
==> Downloading https://homebrew.bintray.com/bottles/mecab-ipadic-2.7.0-20070801.mojave.bottle.tar.gz
Already downloaded: /Users/rob/Library/Caches/Homebrew/downloads/\[...\]--mecab-ipadic-2.7.0-20070801.mojave.bottle.tar.gz
==> Downloading https://homebrew.bintray.com/bottles/msgpack-3.2.1.mojave.bottle.tar.gz
Already downloaded: /Users/rob/Library/Caches/Homebrew/downloads/\[...\]--msgpack-3.2.1.mojave.bottle.tar.gz
==> Downloading https://homebrew.bintray.com/bottles/pcre-8.44.mojave.bottle.tar.gz
Already downloaded: /Users/rob/Library/Caches/Homebrew/downloads/\[...\]--pcre-8.44.mojave.bottle.tar.gz
==> Downloading https://homebrew.bintray.com/bottles/groonga-10.0.2.mojave.bottle.tar.gz
Already downloaded: /Users/rob/Library/Caches/Homebrew/downloads/\[...\]--groonga-10.0.2.mojave.bottle.tar.gz
==> Downloading https://downloads.mariadb.com/MariaDB/mariadb-10.4.13/source/mariadb-10.4.13.tar.gz
######################################################################## 100.0%
==> Installing dependencies for mariadb: cmake, pkg-config, mecab, mecab-ipadic, msgpack, pcre and groonga
==> Installing mariadb dependency: cmake
==> Pouring cmake-3.17.2.mojave.bottle.tar.gz
==> Caveats
Emacs Lisp files have been installed to:
  /usr/local/share/emacs/site-lisp/cmake
==> Summary
🍺  /usr/local/Cellar/cmake/3.17.2: 6,156 files, 58.1MB
==> Installing mariadb dependency: pkg-config
==> Pouring pkg-config-0.29.2\_3.mojave.bottle.tar.gz
🍺  /usr/local/Cellar/pkg-config/0.29.2\_3: 11 files, 623.6KB
==> Installing mariadb dependency: mecab
==> Pouring mecab-0.996.mojave.bottle.3.tar.gz
🍺  /usr/local/Cellar/mecab/0.996: 20 files, 4.2MB
==> Installing mariadb dependency: mecab-ipadic
==> Pouring mecab-ipadic-2.7.0-20070801.mojave.bottle.tar.gz
==> Caveats
To enable mecab-ipadic dictionary, add to /usr/local/etc/mecabrc:
  dicdir = /usr/local/lib/mecab/dic/ipadic
==> Summary
🍺  /usr/local/Cellar/mecab-ipadic/2.7.0-20070801: 16 files, 50.6MB
==> Installing mariadb dependency: msgpack
==> Pouring msgpack-3.2.1.mojave.bottle.tar.gz
🍺  /usr/local/Cellar/msgpack/3.2.1: 757 files, 5.2MB
==> Installing mariadb dependency: pcre
==> Pouring pcre-8.44.mojave.bottle.tar.gz
🍺  /usr/local/Cellar/pcre/8.44: 204 files, 5.5MB
==> Installing mariadb dependency: groonga
==> Pouring groonga-10.0.2.mojave.bottle.tar.gz
🍺  /usr/local/Cellar/groonga/10.0.2: 886 files, 39.5MB
==> Installing mariadb
==> cmake . -DMYSQL\_DATADIR=/usr/local/var/mysql -DINSTALL\_INCLUDEDIR=include/mysql -DINSTALL\_MANDIR=share/man -DINSTALL\_DOCDIR=share/d
==> make
==> make install
==> Not running post\_install as we're building a bottle
You can run it manually using \`brew postinstall mariadb\`
==> Caveats
A "/etc/my.cnf" from another install may interfere with a Homebrew-built server starting up correctly.
 
MySQL is configured to only allow connections from localhost by default
 
To have launchd start mariadb now and restart at login:
  brew services start mariadb
Or, if you don't want/need a background service you can just run:
  mysql.server start
==> Summary
🍺  /usr/local/Cellar/mariadb/10.4.13: 737 files, 170.0MB, built in 8 minutes 53 seconds
Removing: /usr/local/Cellar/mariadb/10.2.14... (641 files, 168.6MB)
Removing: /usr/local/Cellar/mariadb/10.3.10... (652 files, 173.3MB)
Removing: /Users/rob/Library/Caches/Homebrew/mariadb--10.3.10.tar.gz... (67.2MB)
==> Checking for dependents of upgraded formulae...
==> No dependents found!
==> Caveats
==> cmake
Emacs Lisp files have been installed to:
  /usr/local/share/emacs/site-lisp/cmake
==> mecab-ipadic
To enable mecab-ipadic dictionary, add to /usr/local/etc/mecabrc:
  dicdir = /usr/local/lib/mecab/dic/ipadic
==> mariadb
A "/etc/my.cnf" from another install may interfere with a Homebrew-built
server starting up correctly.
 
MySQL is configured to only allow connections from localhost by default
 
To have launchd start mariadb now and restart at login:
  brew services start mariadb
Or, if you don't want/need a background service you can just run:
  mysql.server start


7. Run the database installer​

Run mysql_install_db. Follow on-screen instructions to upgrade if necessary to upgrade a previously installed version.

#初始化 安装数据库 命令：
$ mysql_install_db
# 安装的结果内容
zhoujh@zhoujhdeMBP ~ % mysql_install_db 
Installing MariaDB/MySQL system tables in '/usr/local/var/mysql' ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system


Two all-privilege accounts were created.
# 注意事项：默认创建的账户信息：root 和 zhoujh
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is zhoujh@localhost, it has no password either, but
you need to be the system 'zhoujh' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo

See the MariaDB Knowledgebase at https://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '/usr/local/Cellar/mariadb/10.5.6' ; /usr/local/Cellar/mariadb/10.5.6/bin/mysqld_safe --datadir='/usr/local/var/mysql'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/local/Cellar/mariadb/10.5.6/mysql-test' ; perl mysql-test-run.pl

Please report any problems at https://mariadb.org/jira

The latest information about MariaDB is available at https://mariadb.org/.
You can find additional information about the MySQL part at:
https://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/

# 如果已经有数据库 则可以通过执行更新命令 
$ mysql_upgrade
Phase 1/7: Checking and upgrading mysql database
Processing databases
...
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK

8. Start MariaDB​

Run mysql.server start.

$ mysql.server start
Starting MySQL
. SUCCESS!

9. Secure the installation​

If you are installing MariaDB 10.4.6 or later:
Run mariadb-secure-installation.
If you are installing an earlier version of MariaDB:
Run mysql_secure_installation.

NOTE: If you are unsure about using unix_socket, do not enable it when asked.
NOTE: Set a root password even if the on-screen instructions tell you it is safe not to do so.

$ mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.
 
Enter current password for root (enter for none): 
OK, successfully used password, moving on
 
Setting the root password or using the unix\_socket ensures that nobody can log into the MariaDB root user without the proper authorisation.
Enable unix\_socket authentication? \[Y/n\] n
 ... skipping.
 
You already have your root account protected, so you can safely answer 'n'.
 
Change the root password? \[Y/n\] y
New password:
Re-enter new password:
Password updated successfully! 
 
By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them.  This is intended only for testing, and to make the installation go a bit smoother.  You should remove them before moving into a production environment.
 
Remove anonymous users? \[Y/n\] y
 ... Success!
 
Normally, root should only be allowed to connect from 'localhost'.  This ensures that someone cannot guess at the root password from the network.
 
Disallow root login remotely? \[Y/n\] y
 ... Success!
 
By default, MariaDB comes with a database named 'test' that anyone can access.  This is also intended only for testing, and should be removed before moving into a production environment.
 
Remove test database and access to it? \[Y/n\] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!
 
Reloading the privilege tables will ensure that all changes made so far will take effect immediately.
 
Reload privilege tables now? \[Y/n\] y
 ... Success!
 
Cleaning up...
 
All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.
 
Thanks for using MariaDB!


10. Connect to MariaDB​

Run mariadb -u root -p.

If you’ve installed an older version of mariadb you may need to use “mysql -u root -p” in the above command.

$ mariadb -u root -p 
Enter password: 
Welcome to the MariaDB monitor. Commands end with ; or \\g. Your MariaDB connection id is 52 Server version: 10.4.13-MariaDB Homebrew
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.
MariaDB \[(none)\]>

指定datadir​

I think one can end up in this position with older versions of mysql already installed. I had the same problem and none of the above solutions worked for me. I fixed it thus:

Used brew's remove & cleanup commands, unloaded the launchctl script, then deleted the mysql directory in /usr/local/var, deleted my existing /etc/my.cnf (leave that one up to you, should it apply) and launchctl plist

Updated the string for the plist. Note also your alternate security script directory will be based on which version of MySQL you are installing.

Step-by-step​

brew remove mysql
brew cleanup
launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
sudo rm -rf /usr/local/var/mysql

I then started from scratch:

  1. installed mysql with brew install mysql

  2. ran the commands brew suggested: (see note: below)

    unset TMPDIR
    mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp

  3. Start mysql with mysql.server start command, to be able to log on it

  4. Used the alternate security script:

    /usr/local/Cellar/mysql/5.5.10/bin/mysql_secure_installation

  5. Followed the launchctl section from the brew package script output such as,

    #start
    launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
    #stop
    launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

Note: the --force bit on brew cleanup will also cleanup outdated kegs, think it's a new-ish homebrew feature.

Note the second: a commenter says step 2 is not required. I don't want to test it, so YMMV!

Here are detailed instructions combining getting rid of all MySQL from your Mac then installing it The Brew Way as Sedorner wrote above:

Remove MySQL completely per The Tech Lab​

  • ps -ax | grep mysql
  • stop and kill any MySQL processes
  • 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
  • edit /etc/hostconfig and remove the line 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*
  • sudo rm -rf /tmp/mysql*
  • try to run mysql, it shouldn't work

Brew install MySQL per user Sedorner from this StackOverflow answer​

  • brew doctor and fix any errors

  • brew remove mysql

  • brew cleanup

  • brew update

  • brew install mysql

  • unset TMPDIR

      mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp # whoami is executed inline

  • mysql.server start

  • run the commands Brew suggests, add MySQL to launchctl so it automatically launches at startup

mysql should now work and be running all the time as expected

Godspeed.

Had the same problem. Seems like there is something wrong with the set up instructions or the initial tables that are being created. This is how I got mysqld running on my machine.

If the mysqld server is already running on your Mac, stop it first with:

launchctl unload -w ~/Library/LaunchAgents/com.mysql.mysqld.plist

Start the mysqld server with the following command which lets anyone log in with full permissions.

mysqld_safe --skip-grant-tables

Then run mysql -u root which should now let you log in successfully without a password. The following command should reset all the root passwords.

UPDATE mysql.user SET Password=PASSWORD('NewPassword') WHERE User='root'; FLUSH PRIVILEGES;

Now if you kill the running copy of mysqld_safe and start it up again without the skip-grant-tables option, you should be able to log in with mysql -u root -p and the new password you just set.