mysql学习-生产配置文件示例以及分区配置

生产上mysql配置文件my.cnf

[mysqld]
#default-character-set=utf8
basedir=/data/mysql
datadir=/data/mysql/data
#skip-grant-tables
lower_case_table_names=1
socket=/tmp/mysql.sock
port=3306
server-id=2
innodb_flush_log_at_trx_commit=0
innodb_autoextend_increment=128M
innodb_log_buffer_size=20M
innodb_log_file_size=128M
innodb_buffer_pool_size=15240M
back_log=500
wait_timeout=18000
interactive_timeout=18000
max_connections=3000
max_connect_errors = 6000
query_cache_size=50M
query_cache_type = ON
read_buffer_size=4M
sort_buffer_size=4M
read_rnd_buffer_size=8M
symbolic-links=0
open_files_limit  = 5000
tmp_table_size=56M
binlog_cache_size = 1M
thread_cache_size=64
log-error=/data/mysql/log/mysqld.log
pid-file=/data/mysql/data/mysql.pid
binlog_format=mixed
expire_logs_days=10
character-set-server=utf8
#满查询
slow_query_log= ON
slow_query_log_file=/var/log/slow.log
long_query_time = 3
#同步延迟
innodb_flush_log_at_trx_commit=0
read_only=1
sync_binlog=0
slave_parallel-type=LOGICAL_CLOCK
slave_parallel_workers=6
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
#event_scheduler=ON
replicate_wild_ignore_table=mysql.%
slave-skip-errors=all
slave-skip-errors = 1062,1032

手动建立分区语句

1.手动建立一个分区

执行函数：
DROP PROCEDURE IF EXISTS `proc_create_partion_vehiclescores`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_create_partion_vehiclescores`()
BEGIN
/* 到系统表查出这个表的最大分区，得到最大分区的日期。在创建分区的时候，名称就以日期格式存放，方便后面维护 */
    SELECT REPLACE(partition_name,'p_vehiclescores','') INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS 
    WHERE table_name='vehiclescores' ORDER BY partition_ordinal_position DESC LIMIT 1;
     SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL 1 DAY))+0;
/* 修改表，在最大分区的后面增加一个分区，时间范围加1天 */
    SET @s1=CONCAT('ALTER TABLE vehiclescores ADD PARTITION (PARTITION p_vehiclescores',@Max_date,' VALUES LESS THAN (TO_DAYS (''',DATE(@Max_date),''')))');
    /* 输出查看增加分区语句*/
    SELECT @s1;
    PREPARE stmt2 FROM @s1;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;
/* 取出最小的分区的名称，并删除掉 。
    注意：删除分区会同时删除分区内的数据，慎重 */
    /*select partition_name into @P0_Name from INFORMATION_SCHEMA.PARTITIONS 
    where table_name='tb_3a_huandan_detail' order by partition_ordinal_position limit 1;
    SET @s=concat('ALTER TABLE tb_3a_huandan_detail DROP PARTITION ',@P0_Name);
    PREPARE stmt1 FROM @s; 
    EXECUTE stmt1; 
    DEALLOCATE PREPARE stmt1; */
END
;;
DELIMITER ;