mysql percona-server-5.7.32-35 简单介绍

编译参数：（使用非root用户，本例中使用www，请按实际修改安装目录及用户）

cmake \
-DCMAKE_INSTALL_PREFIX=/data1/server/mysql \
-DDEFAULT_CHARSET=utf8mb4 \
-DDEFAULT_COLLATION=utf8mb4_general_ci \
-DEXTRA_CHARSETS=complex \
-DWITH_SSL=system \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLED_LOCAL_INFILE=1 \
-DENABLE_DTRACE=0 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DCMAKE_EXE_LINKER_FLAGS="-ljemalloc" \
-DWITH_SAFEMALLOC=OFF \
-DMYSQL_USER=www \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=/usr/local/boost \
-DWITH_SYSTEMD=1
 
make -j8
make install

初始化&修改默认密码

su www
cd /data1/server/mysql/bin
./mysqld --initialize --basedir=/data1/server/mysql --datadir=/data1/mysql/3316/
####注此步骤会设置默认密码，需修改
A temporary password is generated for root@localhost: 3%3:xql*Oj.l
 
wget http://192.168.5.123:8081/my5.7.cnf -O /data1/mysql/3316//my.cnf
####注请修改文件中的相关信息，此文件只适配于当前环境
 
启动
./mysqld --defaults-file=/data1/mysql/3316/my.cnf &
 
修改root默认密码，不修改不能使用
/data1/server/mysql/bin/mysql -uroot -p -S /data1/mysql/3316/mysql.sock
alter user root@localhost identified by 'MyRootKey';
 
以下操作为非必须
#创建mysql命令链接
ln -s /data1/server/mysql/bin/mysql /usr/bin/
ln -s /data1/server/mysql/bin/mysqld /usr/bin/
ln -s /data1/server/mysql/bin/mysqladmin /usr/bin/
ln -s /data1/server/mysql/bin/mysqldump /usr/bin/

# 创建用户
create user 'username'@'host' identified by 'password';
# 更改用户密码
set password for 'username'@'host' = password('new_password');
# 删除用户
drop user 'username'@'localhost'

# 查看用户权限
show grants for 'username'@'host';
# 授权
grant privileges on DATABASE_NAME.TABLE_NAME to 'username'@'host';
flush privileges;

# 授权用户对某个数据库的所有表拥有所有操作权限
grant all privileges on 'db_name'.* to 'username'@'host';
# 对某个数据库某个表进行某些操作的授权
grant select, insert, update on 'db_name'.'table_name' to 'username'@'host';

# 撤销授权
revoke privilege on DATABASE_NAME.TABLE_NAME from 'username'@'host';

vim my.cnf 注：mysqld_safe已废弃，需复制到mysqld 下，#innodb_additional_mem_pool_size参数已废弃，注释即可

[mysqld_safe]
nice = 0
pid-file = /data1/mysql/3316/mysqld.pid

[mysqld]
user = www
socket = /data1/mysql/3316/mysql.sock
port = 3316
basedir = /data1/server/mysql
datadir = /data1/mysql/3316
tmpdir = /data1/tmp
pid-file = /data1/mysql/3316/mysqld.pid
skip-external-locking
skip-name-resolve
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 256
myisam-recover-options = BACKUP
max_connections = 2000
table_open_cache = 4096
back_log = 1024
max_connect_errors = 100000
thread_handling=pool-of-threads
thread_pool_oversubscribe = 10
lower_case_table_names = 1
wait_timeout= 864000
slave-net-timeout=5

key_buffer_size = 512M
sort_buffer_size = 4M
join_buffer_size = 4M

read_buffer_size = 4M
read_rnd_buffer_size = 8M
max_heap_table_size = 128M
tmp_table_size = 128M
myisam_sort_buffer_size = 128M
bulk_insert_buffer_size = 256M

open_files_limit = 65535

query_cache_limit = 2M
query_cache_size = 256M

log_error = /data1/mysql/3316/mysql-error.log

slow-query-log
slow-query-log-file = /data1/mysql/3316/mysql-slow.log
long_query_time = 1

server-id = 1
log_bin = mysql-bin
log_bin = /data1/mysql/3316/mysql-bin.log
binlog_format = row
binlog_cache_size = 2M
expire_logs_days = 15
max_binlog_size = 1000M

binlog_ignore_db = mysql
replicate_wild_ignore_table = mysql.%
relay-log = mysqld-relay-bin
relay_log_info_repository = TABLE
master_info_repository = TABLE
relay_log_recovery = on

#read_only = 1

innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 4
#innodb_additional_mem_pool_size = 128M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_log_buffer_size = 16M
innodb_open_files = 4000
innodb_read_io_threads = 24
innodb_write_io_threads = 24
innodb_thread_concurrency=24
innodb_io_capacity = 2000
innodb_io_capacity_max=6000
innodb_lru_scan_depth=2000
innodb_max_dirty_pages_pct = 85
innodb_flush_log_at_trx_commit = 2
transaction-isolation = READ-COMMITTED

sync_binlog=20
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
show_compatibility_56 = 1 

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]

[isamchk]
key_buffer = 128M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M

错误集合

错误1、ERROR 3167 (HY000) at line 1: The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56'
	解决1： 命令查看show_compatibility_56其值
		mysql> show variables like '%show_compatibility_56%';
		+-----------------------+-------+
		| Variable_name         | Value |
		+-----------------------+-------+
		| show_compatibility_56 | OFF   |
		+-----------------------+-------+
		1 row in set (0.01 sec)
		
		mysql> set global show_compatibility_56=on;
		Query OK, 0 rows affected (0.00 sec)

		mysql> show variables like '%show_compatibility_56%';
		+-----------------------+-------+
		| Variable_name         | Value |
		+-----------------------+-------+
		| show_compatibility_56 | ON    |
		+-----------------------+-------+
		1 row in set (0.00 sec)
	 解决2：永久性解决，在cnf配置文件中添加，需重启
		show_compatibility_56 = 1

错误2、[Err] 1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column 'curtime' at row 1
	解决：删除配置文件中sql_mode值中的NO_ZERO_DATE和NO_ZERO_IN_DATE

错误3、[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause
	解决：删除配置文件中sql_mode值中的ONLY_FULL_GROUP_BY
	# 修改全局
		set @@global.sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
	# 修改当前
		set @@sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

mysql的sql_mode

​​​​​​​sql_mode是个很容易被忽视的变量，默认值是空值，在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式，所以开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题 

sql_mode常用值如下： 

ONLY_FULL_GROUP_BY：
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中

NO_AUTO_VALUE_ON_ZERO：
该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。

STRICT_TRANS_TABLES：
在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制

NO_ZERO_IN_DATE：
在严格模式下，不允许日期和月份为零

NO_ZERO_DATE：
设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。

ERROR_FOR_DIVISION_BY_ZERO：
在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如 果未给出该模式，那么数据被零除时MySQL返回NULL

NO_AUTO_CREATE_USER：
禁止GRANT创建密码为空的用户

NO_ENGINE_SUBSTITUTION：
如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

PIPES_AS_CONCAT：
将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似

ANSI_QUOTES：
启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

ORACLE的sql_mode设置等同：PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES'