MySQL服务管理

编辑本随笔

MAC OS安装

 

 我们直接下载PKG安装包进行安装，按官方说法是10.2版本开始有pkg安装包，实际也只有10.2.x版本存在，https://downloads.mariadb.com/MariaDB/

 

下载后直接安装即可，会自动安装在/usr/local/mariadb/中

配置文件在

/usr/local/etc/my.cnf

/usr/local/etc/my.cnf.default

/usr/local/etc/my.cnf.d/

/usr/local/mariadb/etc/my.cnf

安装完成后用 sudo /usr/local/mariadb/server/bin/mysql_secure_installation初始化数据库，一定要用sudo提权

MariaDB默认绑定到127.0.0.1，并禁用了TCP连接，需要将其关闭

关闭仅限本地访问：

sudo vi /usr/local/etc/my.cnf.default

注释掉bind-address

 

启用网络连接：

sudo vi /usr/local/mariadb/etc/my.cnf

注释掉skip-networking

 

启动时提示无法用root启用，加上--user=mysql即可

 

select user();#查看当前登录用户

授权用户远程登录：

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

flush privileges;刷新授权表

mysql -uroot -p -h 127.0.0.1 -P 3306;指定mysql客户端登录指定服务器

密码破解修改：

统一字符编码：

登录后用\s查看字符编码

mysql> \s
--------------
mysql Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapper

Connection id: 21
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.22-0ubuntu0.17.10.1 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 47 min 14 sec

Threads: 1 Questions: 34 Slow queries: 0 Opens: 110 Flush tables: 1 Open tables: 29 Queries per second avg: 0.011

统一字符编码为utf-8

修改配置文件/etc/mysql/my.cnf

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

修改后重启服务service mysql restart,/etc/init.d/mysql restart

查看字符编码

mysql Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapper

Connection id: 3
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.22-0ubuntu0.17.10.1 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 15 sec

Threads: 1 Questions: 5 Slow queries: 0 Opens: 107 Flush tables: 1 Open tables: 26 Queries per second avg: 0.333
--------------

Windows安装

1、安装

官网下载zip格式文件

  2、初始化

#初始化数据目录，指定安装服务名称，指定root密码
mysql_install_db.exe --service=MariaDB.10.4 --password=123.com