centos 7 mysql 创建用户_Centos7 编译安装mysql 8.0.11 及处理创建用户的问题

创建mysql用户

# useradd mysql -s /sbin/nologin

创建程序目录、数据目录、日志目录和boost目录

# mkdir -p /server/mysql

# mkdir -p /server/data

# mkdir -p /server/logs/mysql

# mkdir -p /server/boost

更改目录权限

# chown -R mysql:mysql /server/data/

# chown -R mysql:mysql /server/logs/mysql

下载 mysql 8.0.11 的源码

安装基础环境

# yum -y install make gcc-c++ cmake bison-devel ncurses-devel bison perl perl-devel perl perl-devel openssl-devel

编译安装

# tar zxvf mysql-boost-8.0.11.tar.gz

# cmake -DCMAKE_INSTALL_PREFIX=/server/mysql -DMYSQL_UNIX_ADDR=/server/mysql/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DMYSQL_DATADIR=/server/data -DMYSQL_TCP_PORT=3306 -DWITH_BOOST=/server/boost -DDOWNLOAD_BOOST=1

# make && make install

更改my.cnf配置

# vim /etc/my.cnf

[client]

port = 3306

socket = /server/data/mysql.sock

[mysqld]

mysqlx-port=33060                                     # 特别注意：8.0默认增加了X plugin

mysqlx-socket=/server/data/mysqlx.sock

port=3306

socket=/server/data/mysql.sock

basedir=/server/mysql

datadir=/server/data

pid-file=/server/data/mysql.pid

lower_case_table_names=1

max_connections=400

max_connect_errors=100

max_allowed_packet = 128M

wait-timeout = 10

interactive-timeout = 20

character_set_server=utf8

server-id=1

log_error=/server/logs/mysql/mysql.log.error

slow_query_log=1

slow_query_log_file=/server/logs/mysql/mysql.log.slow

long_query_time=1

log-bin=master-bin

binlog_format=mixed

general_log=ON

general_log_file=/server/logs/mysql/mysql.log

log_timestamps = system

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

local-infile = 1

初始化数据库

# /server/mysql/bin/mysqld –initialize-insecure –user=mysql –basedir=/server/mysql –datadir=/server/data

设置root密码

# /server/mysql/bin/mysql -uroot -p

mysql> show databases;

发现只有三个库，感觉有问题

mysql> use mysql;

Database changed

mysql> show tables;

+——————–+

| Tables_in_mysql |

+——————–+

| innodb_index_stats |

| innodb_table_stats |

+——————–+

只有两个表，问题大大的有

mysql> SELECT DISTINCT CONCAT(‘User: ”’,user,”’@”’,host,”’;’) AS query FROM mysql.user;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect…

Connection id: 10

Current database: mysql

ERROR 1146 (42S02): Table ‘mysql.user’ doesn’t exist

各种操作之后发现是表不全的问题，查看mysql错误日志，里面要求

[ERROR] [MY-010735] [Server] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.

[ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we’re sending the information to the error-log instead: MY-001146 – Table ‘mysql.component’ doesn’t exist

[ERROR] [MY-010326] [Server] Fatal error: Can’t open and lock privilege tables: Table ‘mysql.user’ doesn’t exist

[ERROR] [MY-010353] [Server] Can’t open and lock privilege tables: Table ‘mysql.servers’ doesn’t exist

[ERROR] [MY-010422] [Repl] Error in checking mysql.slave_master_info repository info type of TABLE.

[ERROR] [MY-010415] [Repl] Error creating master info: Error checking repositories.

[ERROR] [MY-010426] [Repl] Slave: Failed to initialize the master info structure for channel ”; its record may still be present in ‘mysql.slave_master_info’ table, consider deleting it.

按照要求执行了 mysql_upgrade 没有作用

最后决定，删除数据重新生成数据

# rm -rf /server/data/*

# /server/mysql/bin/mysqld –initialize-insecure –user=mysql –basedir=/server/mysql –datadir=/server/data

# 再次登陆

mysql> show databases;

mysql> use mysql;

Database changed

mysql> show tables;

+—————————+

| Tables_in_mysql |

+—————————+

| columns_priv |

| component |

| db |

| default_roles |

| engine_cost |

| func |

| general_log |

| global_grants |

| gtid_executed |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| innodb_index_stats |

| innodb_table_stats |

| password_history |

| plugin |

| procs_priv |

| proxies_priv |

| role_edges |

| server_cost |

| servers |

| slave_master_info |

| slave_relay_log_info |

| slave_worker_info |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

+—————————+

33 rows in set (0.00 sec)

这下mysql正常了

mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘newpassword’;

mysql> flush privileges;

新密码设置完毕

设置启动脚本

# vim /lib/systemd/system/mysql.service

[Unit]

Description=MySQL Server

Documentation=man:mysqld(8)

Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html

After=network.target

After=syslog.target

[Install]

WantedBy=multi-user.target

[Service]

User=mysql

Group=mysql

Type=simple

ExecStart=/server/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf –basedir=/server/mysql –datadir=/server/data/mysql

LimitNOFILE = 5000

创建用户aaa

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON aaa.* TO aaa@localhost IDENTIFIED BY ‘aaaaaaaaaaaaa’;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘IDENTIFIED BY ‘aaaaaaaaaaaaa” at line 1

报错是因为myslq 8.0不再支持grant直接创建用户，改成需要先创建用户再授权

mysql> create user aaa@localhost IDENTIFIED BY ‘aaaaaaaaaaaaa’;

mysql> flush privileges;

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON aaa.* TO aaa@localhost;

下述语句的意思是aaa可以把权限给别的用户

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON aaa.* TO aaa@localhost with grant option;

mysql> flush privileges;

这样就完成mysql 8.0.11的安装及创建用户