安装Percona Server 8.0版本数据库

作为MYSQL的衍生社区版本,Percona以其优秀的迭代速度和有异于ORACLE社区版及MARIA DB版本的新特性,给数据开发者带来了更多的选择。

2018年Percona也升级了其下的Percona Server 8.0。这里演示如何在CentOS下安装Percona Server8.0及启用特色存储引擎.

1.安装Percona YUM源:

$ sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

2.设定Yum源使用Percona的版本为8.0

$ sudo percona-release setup ps80

3.安装数据库

$ sudo yum install percona-server-server

4.运行Percona Server for MySQL

$ sudo service mysql start

5.确认运行状态

$ sudo service mysql status

6.关闭Percona或重启Percona

$ sudo service mysql stop
$ sudo service mysql restart

7.找到安装临时root密码

 grep "A temporary password" /var/log/mysqld.log

你会得到结果临时密码;但是如果无法出现可以试着重启Percona Server

2019-01-04T16:56:48.430540Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 临时密码

8.初始化数据库,修改默认密码,注意8.0默认有强制密码强度要求。

mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:
The 'validate_password' component is installed on the server.
The subsequent steps will run with the existing configuration
of the component.
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) :

 ... skipping.
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!

9.解决phpMyadmin因加密认证不一样导致无法连接的问题

mysql -p
Enter password:
mysql> alter user 'root'@'localhost' identified with mysql_native_password by '你的密码';
Query OK, 0 rows affected (0.26 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

CentOS7系统YUM安装MariaDB10.3

1.增加MariaDB的repo源:

# MariaDB 10.3 CentOS repository list - created 2018-05-26 07:55 UTC  
# http://downloads.mariadb.org/mariadb/repositories/  
[mariadb]  
name = MariaDB  
baseurl = http://yum.mariadb.org/10.3/centos7-amd64  
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB  
gpgcheck=1

2.清除Yum缓存,重建缓存

yum clean all 
yum makecache

3.查询MariaDB源中的软件包

yum list --disablerepo=\* --enablerepo=mariadb

其中test为测试工具,backup为备份工具

4.安装安装MariaDB数据库

yum install MariaDB-client MariaDB-server MariaDB-devel -y

5.启动数据库及设置MariaDB开机自启

systemctl start mariadb  
systemctl enable mariadb

6.初始化数据库,并删除测试数据库及更改权限和设置密码

mysql_secure_installation

第一步为输入密码,首次安装未设定默认为空,直接Enter

Change the root password? [Y/n] 该项为询问是否设置新密码。建议设置新密码。其他项目按照默认Y即可。

7.测试进入MariaDB

 mysql -uroot -p -A  
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2894
Server version: 10.3.7-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> 

 

使用Percona安装tokudb存储引擎

由分形树索引支持的PerconaTokuDB®是一款开源的,用于Percona Server的高性能存储引擎,可提高可扩展性和运行效率。专为满足大数据应用的苛刻要求而设计,Percona TokuDB降低了与压缩和优化工作相关的部署成本。Percona TokuDB和Percona HotBackup包含在下面的Percona Server for MySQL 下载链接中。

快速和可扩展性:

Percona TokuDB极大地提高了性能和并发性,减少了磁盘和闪存驱动器存储需求,支持在线模式更改并支持完全符合ACID的事务

Percona TokuDB可以:

  • 更好的性能:如果不进行调整,响应时间比InnoDB快20倍
  • 更强的投资回报率:运营和基础设施成本更低,可扩展性和灵活性更高
  • 可用性更高:在公共,私人和混合云环境中对苛刻应用的停机时间更少

1.安装percona官方RPM源

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

2.测试Percona源

yum list | grep percona

3.yum安装percona

yum install Percona-Server-server-57

4.Percona默认管理命令

service mysql start 
service mysql restart 
service mysql stop 
service mysql status

5.查询安装后的临时percona root密码

grep ‘temporary password’ /var/log/mysqld.log

6.修改数据库密码

mysql -u root -p
Enter password:yourpasswd
elcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 92
Server version: 5.7.21-21-log Percona Server (GPL), Release 21, Revision 2a37e4e

Copyright (c) 2009-2018 Percona LLC and/or its affiliates
Copyright (c) 2000, 2018, 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.

Percona [(none)]> set password=password(‘yournewpasswd’);

mysql有密码安全机制,注意设置为足够复杂的密码

7.编辑/etc/mycnf配置文件,修改mysql默认编码

[mysqld] 
character_set_server=utf8

8.安装jemalloc

yum install jemalloc 
rpm -qa |grep jemalloc 
rpm -qa |grep jemalloc 
jemalloc-3.6.0-1.el7.x86_64
rpm -ql jemalloc-3.6.0-1.el7.x86_64
/usr/bin/jemalloc.sh
/usr/lib64/libjemalloc.so.1
/usr/share/doc/jemalloc-3.6.0
/usr/share/doc/jemalloc-3.6.0/COPYING
/usr/share/doc/jemalloc-3.6.0/README
/usr/share/doc/jemalloc-3.6.0/VERSION
/usr/share/doc/jemalloc-3.6.0/jemalloc.html

记录下/usr/lib64/libjemalloc.so.1地址

9.配置jemalloc依赖文件,编辑/etc/my.cnf,其中加入以下内容:

[mysqld_safe] 
malloc-lib=/usr/lib64/libjemalloc.so.1

10.检查Transparent huge pages

echo never > /sys/kernel/mm/transparent_hugepage/enabled 
echo never > /sys/kernel/mm/transparent_hugepage/defrag

11.安装tokudb

yum install Percona-Server-tokudb-57.x86_64

12.修改selinux状态为permissive,编辑编辑/etc/selinux/config修改以下内容:

selinux=permissive
setenforce 0

12.初始设置tokudb

ps_tokudb_admin --enable -uroot -p

输入密码,检查是否成功

13.重启percona

service mysql restart

14.检查确认是否启用tokudb

mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 94
Server version: 5.7.21-21-log Percona Server (GPL), Release 21, Revision 2a37e4e

Copyright (c) 2009-2018 Percona LLC and/or its affiliates
Copyright (c) 2000, 2018, 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.

Percona [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |
| TokuDB             | DEFAULT | Percona TokuDB Storage Engine with Fractal Tree(tm) Technology             | YES          | YES  | YES        |
| InnoDB             | YES     | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                             | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.07 sec)

看到tokudb即为成功。

phpmyadmin与php.ini中的socket配置问题

昨天在安装完新的perconesql数据后,发现phpmyadmin不能正常连接了。一时查来查去不知出了什么问题。

后来经过确认,由于php.ini中的mysqli.default_socket和pdo_mysql.default_socke默认为空,实际默认即为/tmp/mysql.sock,此时如果和mysql本身的设置不一致,就会导致phpmyadmin无法有效登录。

我们先查询mysql服务器使用的socket位置:

# mysql -u root -p
Enter password: yourpasswd
mysql> STATUS;
--------------
mysql  Ver 14.14 Distrib 5.7.21-21, for Linux (x86_64) using  6.2

Connection id:		2
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.7.21-21 Percona Server (GPL), Release 21, Revision 2a37e4e
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/lib/mysql/mysql.sock
Uptime:			26 min 14 sec

Threads: 1  Questions: 5  Slow queries: 0  Opens: 105  Flush tables: 1  Open tables: 98  Queries per second avg: 0.003
--------------

mysql> exit
Bye

上面命令中查询到的socket地址为/var/lib/mysql/mysql.sock

修改php.ini文件中的两项:

vi  /etc/php.ini


mysqli.default_socket = /var/lib/mysql/mysql.sock 



pdo_mysql.default_socket = /var/lib/mysql/mysql.so

你也可以设置mysql配置文件my.cnf

vi /etc/my.cnf

[client]
socket          = /var/lib/mysql/mysql.sock

[mysqld]
socket          = /var/lib/mysql/mysql.sock

至此,重启php或httpd就可以登录phpmyadmin