mysql 图节点_MySQL节点

MySQL 节点

参考 Ubuntu 装机指南，获得一个干净的Server后，下面可以将这个Server做成MySQL节点。

制作 Mysql 节点

1. 安装$ sudo apt-get install mysql-server mysql-client # 安装

$ mysqld --version # 确认版本

mysqld Ver 5.7.28-0ubuntu0.18.04.4 for Linux on x86_64 ((Ubuntu))

$ pgrep -a mysql # 确认进程运行

2247 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

$ sudo netstat -natp # 查看监听的端口

Active Internet connections (servers and established)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 2247/mysqld

$ sudo lsof -i:3306 # 查看 3306 端口的占用情况

COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME

mysqld 2247 mysql 30u IPv4 26625 0t0 TCP localhost:mysql (LISTEN)

$ link@mysql-db-1:~$ sudo dpkg -S mysql | grep conf # 查看软件包安装后的所有文件清单

2. 远程登录$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address = 0.0.0.0 # 修改为本句

$ netstat -natp # 确认下

tcp6 0 0 :::3306 :::* LISTEN -

3. 安全设置$ sudo mysql_secure_installation

New password: # 随便填，因为没效果

Re-enter new password: # 随便填，因为没效果

Remove anonymous users? # y 移除匿名用户

Disallow root login remotely? # y 不给 root 远程登录权限

Remove test database and access to it? # 删除 test 库

新装的MySQL 5.7 Server的root用户并不是密码校验登录，而是限定 linux 的 root 用户才可以登录本机的 mysql-server:$ sudo mysql # 直接就可以登录了

查看字符集和排序:mysql> show character set like '%utf%';

+---------+------------------+--------------------+--------+

| Charset | Description | Default collation | Maxlen |

+---------+------------------+--------------------+--------+

| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |

| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |

| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |

| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |

+---------+------------------+--------------------+--------+

4. 创建一个数据库 xprojectmysql> create database xproject default charset utf8mb4 collate utf8mb4_general_ci;

PS: 使用 utf8mb4 和 utf8mb4_general_ci，避免一切诡异编码问题 ^_^

5. 创建用户 xuser 来管理 xproject 数据库mysql> create user 'xuser'@'%' identified by 'Pass123456';

Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on `xproject`.* to 'xuser'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

6. 在 PC 上验证$ mysql -h192.168.13.3 -uxuser -p

修改 root 用户

新安装的mysql-server的默认用户信息都在/etc/mysql/debian.cnf文件里：[client]

host = localhost

user = debian-sys-maint

password = XUWKDk4UdNhiF5XC

socket = /var/run/mysqld/mysqld.sock

[mysql_upgrade]

host = localhost

user = debian-sys-maint

password = XUWKDk4UdNhiF5XC

socket = /var/run/mysqld/mysqld.sock

然后我们再来设置root用户：mysql -udebian-sys-maint -pXUWKDk4UdNhiF5XC # 通过默认账户进入 MySQL

mysql> select host,user,plugin,authentication_string from mysql.user;

+-----------+------------------+-----------------------+------------------------+

| host | user | plugin | authentication_string |

+-----------+------------------+-----------------------+------------------------+

| localhost | root | auth_socket | |

如果你确实是想用mysql的root用户，使用密码方式验证登录的话，那么执行：mysql> update user set authentication_string = PASSWORD('你的密码'),

mysql> plugin = 'mysql_native_password' where user = 'root';

mysql> flush privileges;