mysql - TuPengXiong/TuPengXiong.github.io GitHub Wiki

安装mysql

    5  yum update
    6  yum list installed | grep mysql
    7  yum -y remove mysql-libs.x86_64 5.1.73-8.el6_8 
    8  yum list installed | grep mysql
    9  cd /usr/local/
   10  wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
   11  ls
   12  rpm -ivh mysql-community-release-el6-5.noarch.rpm
   13  yum list installed | grep mysql
   14  yum repolist all | grep mysql
   15  yum install mysql-community-server
   16  service mysqld start
   17  /usr/bin/mysqladmin -u root password '@2019_888'

主库

CREATE USER 'slave'@'172.16.0.211' IDENTIFIED BY 'slave';

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'172.16.0.211';

FLUSH PRIVILEGES;

SHOW MASTER STATUS;

从库

show slave status;

stop slave;

change master to master_host='172.16.0.216',master_user='slave',master_password='slave',master_log_file='mysql-bin.000001', master_log_pos=1902;

start slave;

权限

grant select,delete,update on *.* to java@"172.16.0.%" identified by "@2019YmKJ";
FLUSH PRIVILEGES;

8h16g my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
port = 3306
server-id = 1
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/mnt/mysql/data
socket=/mnt/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 

back_log=1000
#在MYSQL暂时停止响应新请求之前，短时间内的多少个请求可以被存在堆栈中。如果系统在短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的监听队列的大小。默认值80。

max_connections=4000
#MySQL允许最大的进程连接数，如果经常出现Too Many Connections的错误提示，则需要增大此值。默认151

max_connect_errors=6000
#设置每个主机的连接请求异常中断的最大次数，当超过该次数，MYSQL服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息。默认100

external-locking=FALSE
#使用–skip-external-locking MySQL选项以避免外部锁定。该选项默认开启
max_allowed_packet=64M

thread_cache_size=300
#默认38
## 服务器线程缓存这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。设置规则如下：1GB 内存配置为8，2GB配置为16，3GB配置为32，4GB或更高内存，可配置更大。

query_cache_size=164M
#在MyISAM引擎优化中，这个参数也是一个重要的优化参数。但也爆露出来一些问题。机器的内存越来越大，习惯性把参数分配的值越来越大。这个参数加大后也引发了一系列问题。我们首先分析一下 query_cache_size的工作原理：一个SELECT查询在DB中工作后，DB会把该语句缓存下来，当同样的一个SQL再次来到DB里调用时，DB在该表没发生变化的情况下把结果从缓存中返回给Client。这里有一个关建点，就是DB在利用Query_cache工作时，要求该语句涉及的表在这段时间内没有发生变更。那如果该表在发生变更时，Query_cache里的数据又怎么处理呢？首先要把Query_cache和该表相关的语句全部置为失效，然后在写入更新。那么如果Query_cache非常大，该表的查询结构又比较多，查询语句失效也慢，一个更新或是Insert就会很慢，这样看到的就是Update或是Insert怎么这么慢了。所以在数据库写入量或是更新量也比较大的系统，该参数不适合分配过大。而且在高并发，写入量大的系统，建议把该功能禁掉。

query_cache_limit=4M
#指定单个查询能够使用的缓冲区大小，缺省为1M

table_open_cache=1520

tmp_table_size=67M

thread_cache_size=38

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=67M

default-storage-engine=INNODB

key_buffer_size=290M

read_buffer_size = 1M

read_rnd_buffer_size = 8M

sort_buffer_size = 1M

innodb_additional_mem_pool_size=80M

innodb_flush_log_at_trx_commit=0

innodb_log_buffer_size=8M

innodb_buffer_pool_size=2G

innodb_log_file_size=1024M

innodb_thread_concurrency=18

innodb_support_xa = 1

innodb_file_per_table = 1

innodb_file_format = Barracuda

interactive_timeout = 86400

wait_timeout = 86400



# binlog
#
log_bin=/mnt/mysql/log/mysql-bin.log
# log_bin_index = D:/mysql-5.6.21-winx64/log/binlog/mysql-bin.index
#sync_binlog = 1
expire_logs_days = 7
binlog_format = ROW


[mysqld_safe]
log-error=/mnt/mysql/log/mysqld.log
pid-file=/mnt/mysql/mysqld.pid

[mysqldump]  
max_allowed_packet = 1G

定时任务创建

-- 任务是否开启
show variables like '%event_sche%';

set global event_scheduler=1;

use dc_center

-- 存储过程
DELIMITER $$

CREATE  PROCEDURE `update_config`()
BEGIN

	  SET @sql_text =  "update dc_center.dw_config d set d.value=1 where d.key = 'rechargeVipStatus';";
	  PREPARE stmt FROM @sql_text;
	  EXECUTE stmt;
	  DEALLOCATE PREPARE stmt;  


END$$
DELIMITER ;

-- 创建任务
create event config_event
on schedule at '2019-01-31 06:58:00'
on completion preserve disable
do call update_config();

SELECT event_name,event_definition,execute_at,interval_value,interval_field,status FROM information_schema.EVENTS;

-- 开启定时任务
alter event config_event on completion preserve enable;

-- //关闭定时任务
alter event config_event on completion preserve disable;