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的安装及创建用户

 

相关文章

4 评论

  1. I was curious if you ever thought of changing the
    page layout of your website? Its very well written; I love what youve got to say.
    But maybe you could a little more in the way of content so
    people could connect with it better. Youve got an awful lot of text for only having
    1 or two pictures. Maybe you could space it out better?

  2. Hi! I know this is sort of off-topic but I had to ask. Does managing a well-established blog like yours require a large
    amount of work? I am completely new to operating a blog
    however I do write in my journal on a daily basis.
    I’d like to start a blog so I can share my own experience and views online.

    Please let me know if you have any kind of recommendations or tips for new aspiring bloggers.
    Thankyou!

  3. Magnificent beat ! I would like to apprentice while
    you amend your site, how could i subscribe for a blog web site?
    The account aided me a appropriate deal. I had been tiny bit acquainted of this your
    broadcast offered shiny clear idea

留下评论

error: Content is protected !!