Percona 5.7 安装、建库、授权

服务器信息

服务器：192.168.0.90

操作系统：Cent OS 7.5

一、安装部分

安装方式

一般选择yum安装，原因是没有特殊需求，yum安装比较快

1、关闭selinux及firewalld防火墙

2、安装yum 源

yum install -y https://downloads.percona.com/downloads/percona-release/percona-release-0.1-9/redhat/percona-release-0.1-9.noarch.rpm

如果提示错误，登录percona网址,获取最新realease，或参考官方文档【https://www.percona.com/doc/percona-server/5.7/installation/yum_repo.html】

3、yum 安装

yum install Percona-Server-server-57

4、启动数据库并设置开机启动

# service mysqld restart
# chkconfig mysqld on

5、注意：Percona 5.7安装完默认会产生个随机的密码

查看方法

# cat /var/log/mysqld.log  | grep "A temporary password" | awk -F " " '{print$11}'

6、初始化数据库

[root@localhost Percona-db]# mysql_secure_installation

    Securing the MySQL server deployment.

    Enter password for user root:

    The existing password for the user account root has expired. Please set a new password.

    New password:

    Re-enter new password:
    The 'validate_password' plugin is installed on the server.
    The subsequent steps will run with the existing configuration
    of the plugin.
    Using existing password for root.

    Estimated strength of the password: 100
    Change the password for root ? ((Press y|Y for Yes, any other key for No) : y

    New password:

    Re-enter new password:

    Estimated strength of the password: 100
    Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
    By default, a MySQL installation has an anonymous user,
    allowing anyone to log into MySQL without having to have
    a user account created for them. This is intended only for
    testing, and to make the installation go a bit smoother.
    You should remove them before moving into a production
    environment.

    Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
    Success.


    Normally, root should only be allowed to connect from
    'localhost'. This ensures that someone cannot guess at
    the root password from the network.

    Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
    Success.

    By default, MySQL comes with a database named 'test' that
    anyone can access. This is also intended only for testing,
    and should be removed before moving into a production
    environment.


    Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
    - Dropping test database...
    Success.

    - Removing privileges on test database...
    Success.

    Reloading the privilege tables will ensure that all changes
    made so far will take effect immediately.

    Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
    Success.

    All done!

7、测试

[root@localhost ~]# mysql -uroot -pChane_123
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 12
    Server version: 5.7.11-4 Percona Server (GPL), Release 4, Revision 5c940e1

    Copyright (c) 2009-2016 Percona LLC and/or its affiliates
    Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql>
    mysql>
    mysql>

二、建库及授权部分

1、输入密码后，登录数据库

# mysql -uroot -p

2、本地用户授权

--建表
CREATE DATABASE `new_ibian` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
--建本地用户
CREATE USER 'ceshi_ibian'@'127.0.0.1'IDENTIFIED BY '1234565C7l';
CREATE USER 'ceshi_ibian'@'localhost'IDENTIFIED BY '1234565C7l';
--本地用户授权
GRANT ALL ON new_ibian.* TO 'ceshi_ibian'@'127.0.0.1' WITH GRANT OPTION;
GRANT ALL ON new_ibian.* TO 'ceshi_ibian'@'localhost' WITH GRANT OPTION;
--刷新授权
FLUSH PRIVILEGES;

3、远程用户授权（IP地址为虚拟地址，请勿对号入座）

--建立远程管理用户
CREATE USER 'yogro'@'119.0.190.55'IDENTIFIED BY '06Yw@2im9';
--远程管理用户授权
GRANT ALL ON new_ibian.* TO 'yorgo'@'119.0.190.55' WITH GRANT OPTION;
--刷新授权
FLUSH PRIVILEGES;

三、简易操作

# mysql -u root -p'123@123'　　#登录mysql

# SET PASSWORD FOR root@'localhost' = 'Mcdb@2022!';    #更改root密码

（1） 查看当前实例使用的配置文件是否是你定义的

# mysqld --verbose --help |grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

（2）查看输出日志

# cat /opt/mysql_log/mysql-error.log

（3）查看系统日志

# systemctl status mysqld　　#systemd报错
# journalctl -b　　　　#系统输出日志
# journalctl -xe　　　 #系统输出日志