MYSQL初始化设置

MYSQL初始化设置

首先更改数据库目录：
sudo systemctl stop mysql.service
sudo vim /etc/apparmor.d/usr.sbin.mysqld
修改下列两行

Allow data dir access

   /data/mysql/ r,
   /data/mysql/** rwk,

重启：
sudo /etc/init.d/apparmor reload
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
修改：
datadir = /data/mysql
sudo mkdir -p /data/mysql
sudo chown -R mysql:mysql /data/mysql/
sudo mysqld --initialize

添加root无密码进入：
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
在[mysqld]下添加：
skip-grant-tables
重启：
sudo systemctl restart mysql.service
再更改root密码：
sudo mysql -uroot
mysql>use mysql;
mysql>update user set authentication_string = password(‘'),password_last_changed=now() where user=‘root’;
更改后去掉[mysqld]的配置skip-grant-tables，然后重启。
报错
You must reset your password using ALTER USER statement before executing this statement.
mysql> ALTER USER USER() IDENTIFIED BY '’;
Query OK, 0 rows affected (0.00 sec)

mysql>GRANT ALL PRIVILEGES ON . TO ‘root’@‘%’ IDENTIFIED BY ‘******’;
mysql>FLUSH PRIVILEGES;
mysql>show variables like ‘%data%’;

其次更新数据库账户密码，并且赋予wiki用户在本地或者异地登录时候的所有权限：
mysql> create database confluence CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 1 row affected (0.00 sec)
mysql> create user ‘confluence_user’@‘%’ identified by '';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on confluence. to ‘confluence_user’@‘%’ identified by '';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> grant all privileges on confluence. to ‘confluence_user’@‘localhost’ identified by ‘*******’;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

最后，在配置文件中加入下面内容：
default-storage-engine=INNODB
character_set_server=utf8mb4
innodb_default_row_format=DYNAMIC
innodb_large_prefix=ON
innodb_file_format=Barracuda
innodb_log_file_size=2G
transaction-isolation=READ-COMMITTED
设置开机启动：
sudo systemctl start mysql.service
sudo systemctl enable mysql.service

外部连接111错误

vi /etc/mysql/my.cnf

找到
[mysqld]
bind-address = 127.0.0.1
注释掉bind-address，或改掉ip