Sql配置和安装

发表于 阅读次数：

linux 安装 mariadb

1
lsb_release -a
  • 添加源
1
2
3
sudo apt-get install software-properties-common
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://mirrors.neusoft.edu.cn/mariadb/repo/10.4/ubuntu bionic main'
  • 更新和安装
1
2
sudo apt update
sudo apt install mariadb-server
  • 查看安装状态
1
systemctl status mariadb
  • 查看 mysql version
1
mysql -V

https://linuxize.com/post/how-to-install-mariadb-on-ubuntu-18-04/

uninstall mariadb

1
2
3
4
5
6
apt-get remove --purge mysql*
apt-get remove --purge mysql
apt-get remove --purge mariadb
apt-get remove --purge mariadb*
apt-get --purge remove mariadb-server
apt-get --purge remove python-software-properties

重置密码

How to reset a MySQL root password | Linuxize

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
systemctl stop mysql

sudo mysqld_safe --skip-grant-tables &

mysql -u root

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'lyloou06';
-- SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MY_NEW_PASSWORD');
UPDATE mysql.user SET Password=PASSWORD('rebate@2020') WHERE User='root';
FLUSH PRIVILEGES;

-- restart
mysqladmin -u root -p shutdown
sudo systemctl start mariadb

-- login again
mysql -u root -p

外网登录常见问题

Q: ERROR 2003 (HY000): Can’t connect to MySQL server on (111 “Connection refused”)
A: mysql 远程连接数据库报 111 错误 - ssj901217 的博客 - CSDN 博客

1
2
3
4
5
6
vi /etc/mysql/my.cnf
找到
[mysqld]
bind-address            = 127.0.0.1
注释掉bind-address
重启 mysql, systemctl restart mysql;

Q: ERROR 1130 (HY000): Host is not allowed to connect to this MariaDB server
A: 解决 MySQL ERROR 1130 (HY000): Host ‘XXXX’ is not allowed to connect to this MySQL server - HJULKK 的专栏 - CSDN 博客

1
2
3
4
5
6
7
8
mysql -u root -p
use mysql;
update user set host = '%' where user = 'root';
flush privileges;
select host, user from user;
quit
-- 步骤2：重启mysql,
systemctl restart mysql;

Q: mysql - ERROR 1698 (28000): Access denied for user ‘root‘@’localhost’ - Stack Overflow
A: see below code

1
2
3
4
5
6
7
sudo service mysql restart
sudo mysql # logs in automatically into MariaDB
use mysql;
update user set plugin='' where user='your_user_name';
flush privileges;
exit;
sudo service mysql restart # restarts the mysql service

Centos7.3 安装 Mysql5.7 并修改初始密码_酷玩时刻-By Javen-CSDN 博客_centos7 修改 mysql 密码

linux 安装 mysql

1
2
3
sudo apt-get update
sudo apt-get install mysql-server
sudo mysql_secure_installation

https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-16-04?comment=53320
If it occurs error because of broken MySQL package on Ubuntu 16.04. Just do this trick

1
2
3
4
5
6
7
# Purge all MySQL packages
sudo apt purge mysql*
sudo rm -rf /var/lib/mysql
sudo rm -rf /etc/mysql

# Reinstall MySQL
sudo apt install mysql-server mysql-client

centos

CentOS 7 - 安装 MySQL 5.7-阿里云开发者社区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 添加Mysql5.7仓库
sudo rpm -ivh https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

# 确认Mysql仓库成功添加
sudo yum repolist all | grep mysql | grep enabled
# 开始安装
sudo yum -y install mysql-community-server

# 启动Mysql
sudo systemctl start mysqld

# 系统启动时自动启动
sudo systemctl enable mysqld

# 查看启动状态
sudo systemctl status mysqld

# CentOS上的root默认密码可以在文件/var/log/mysqld.log找到，通过下面命令可以打印出来
cat /var/log/mysqld.log | grep -i 'temporary password'

# 执行下面命令进行安全设置，这个命令会进行设置root密码设置，移除匿名用户，禁止root用户远程连接等
mysql_secure_installation

解决：conflicts between attempted installs of MariaDB-common

1
2
yum shell
remove mariadb-libs

由于 5.7 版本在安装的时候就设置好了，不需要额外设置，但是 5.6 版本建议从安全角度完善下，运行官方脚本即可

设置 root 密码
禁止 root 账号远程登录
禁止匿名账号（anonymous）登录
删除测试库
是否确认修改

默认配置

  • 端口：3306

其它软件

  • mycli

参考资料：

新建用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 创建用户
CREATE USER 'bob'@'%'
    IDENTIFIED BY 'password';

-- 授权1
GRANT ALL 
    ON *.*
    TO 'bob'@'%'
    WITH GRANT OPTION;

-- 授权2
GRANT privileges on databasename.tablename TO 'username'@'host';

-- 修改密码
SET PASSWORD FOR 'bob'@'%' = PASSWORD('bob');

-- 刷新
flush privileges;

MySQL :: MySQL 8.0 Reference Manual :: 6.2.8 Adding Accounts, Assigning Privileges, and Dropping Accounts

networking - MySQL: creating a user that can connect from multiple hosts - Server Fault

1
2
3
4
5
6
7
8
mysql root@120.123.232.2:mysql> GRANT all PRIVILEGES ON *.* to 'root'@'localhost';
(1133, "Can't find any matching row in the user table")

mysql root@120.123.232.2:mysql> GRANT all PRIVILEGES ON *.* to 'root'@'localhost' IDENTIFIED by 'localhost';
Query OK, 0 rows affected
Time: 0.002s

grant all privileges on testDB.* to 'test'@'1.1.1.1'identified by 'pswd';

mac install mariadb

1
2
3
4
5
6
7
8
9
10
11
xcode-select –install
ruby -e “$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)”
brew doctor

brew update
brew info mariadb
brew install mariadb
mysql_install_db
mysql.server start
mysql_secure_installation
mysql -u root -p

mysql 创建数据库,添加用户,用户授权实操方法

https://www.jb51.net/article/172998.htm

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
--创建名称为“testdb”数据库，并设定编码集为utf8
CREATE DATABASE IF NOT EXISTS testdb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
CREATE DATABASE IF NOT EXISTS testdb DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

 --创建了一个名为：test 密码为：1234 的用户
 create user 'test'@'localhost' identified by '1234';
 create user 'rebate'@'%' identified by 'rebate1234';
-- 注意：
-- 此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录。如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录。也可以指定某台机器可以远程登录。

--查询用户
select user,host from mysql.user;

--删除用户“test”
drop user test@localhost ;
--若创建的用户允许任何电脑登陆，删除用户如下
drop user test@'%';


--方法1，密码实时更新；修改用户“test”的密码为“1122”
set password for test =password('1122');
--方法2，需要刷新；修改用户“test”的密码为“1234”
update mysql.user set password=password('1234') where user='test'
--刷新
flush privileges;


--授予用户test通过外网IP对数据库“testdb”的全部权限
grant all privileges on rebate.* to 'test'@'%' identified by '1234';
grant all on rebate.* to rebate@'%' identified by 'rebate1234';

--刷新权限
flush privileges;

--授予用户“test”通过外网IP对于该数据库“testdb”中表的创建、修改、删除权限,以及表数据的增删查改权限
grant create,alter,drop,select,insert,update,delete on testdb.* to test@'%';

GRANT process ON . TO 'test'@'%';
flush privileges;

--查看用户“test”
show grants for test;
show grants for rebate;

简易版本

1
2
3
4
5
6
7
8
9
10
CREATE DATABASE IF NOT EXISTS db_movie DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
create user 'admin'@'%' identified by '123456';
grant all privileges on db_movie.* to 'admin'@'%' identified by '123456';
grant all on db_movie.* to 'admin'@'%' identified by '123456';

grant create,alter,drop,select,insert,update,delete on db_movie.* to 'admin'@'%';
GRANT process ON *.* TO 'admin'@'%' identified by '123456';
flush privileges;

show grants for db_movie;