流 年

最近，朋友的博客经常挂，拜托我上去找下原因，最后发现mysql挂了导致的。

手动启动，发现启动不了，查看日志：

130629  5:46:48 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
130629  5:46:48 InnoDB: Completed initialization of buffer pool
130629  5:46:48 InnoDB: Fatal error: cannot allocate memory for the buffer pool
130629  5:46:48 [ERROR] Plugin 'InnoDB' init function returned error.
130629  5:46:48 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
130629  5:46:48 [ERROR] Unknown/unsupported storage engine: InnoDB
130629  5:46:48 [ERROR] Aborting

从日志中大概能看出内存的问题，百度了一下相关问题，果然是，网上说内存不足导致的，用free命令查看了验证了下，确实如此内存被消耗查不多了，并且系统未分配swap。

于是，解决思路就是增加swap：

……

有时候，我们不想依赖MySQL数据表的主键“自增ID”来作为 “业务ID”

最简陋的方法是：单独数据表 + 函数（存储函数），下面简要说明操作步骤

1.创建生成多个表的序列号的数据维护表

CREATE TABLE seq (
name varchar(20) NOT NULL,
val int(10) UNSIGNED NOT NULL,
PRIMARY KEY  (name)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

2.插入几条初始化数据

INSERT INTO seq VALUES('one',100);
INSERT INTO seq VALUES('two',1000);

3.创建函数以生成序列号

CREATE FUNCTION seq(seq_name char (20)) returns int
begin
 UPDATE seq SET val=last_insert_id(val+1) WHERE name=seq_name;
 RETURN last_insert_id();
end

　　 说明：MySQL中默认的语句结束符为分号（;）。存储过程中的SQL语句需要分号来 结束。为了避免冲突，首先用”DELIMITER &&“将MySQL的结束符设置为&&。最后再用”DELIMITER ;“来将结束符恢复成分号。这与创建触发器时是一样的。

……

1、下载二进制备份文件，解压：略

2、还原数据：

./innobackupex --defaults-file=/data/service/mysql/data/backup-my.cnf --apply-log /data/service/mysql/data

报错：

160408 13:49:57 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

./innobackupex version 2.3.4 based on MySQL server 5.6.24 Linux (x86_64) (revision id: e80c779)
xtrabackup: cd to /data/service/mysql/data
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(2113955558)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:200M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:200M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Log file ./ib_logfile1 is of different size 50331648 bytes than other log files 2097152 bytes!
xtrabackup: innodb_init(): Error occured.

……

一、创建数据库

CREATE DATABASE `{$db_name}` CHARACTER SET utf8 COLLATE utf8_general_ci;

二、刷新MySQL缓存

FLUSH PRIVILEGES;

三、新建 MySQL账户和密码

INSERT INTO mysql.user(Host,User,Password) VALUES('%','{$db_user}',PASSWORD('{$db_password}')); 

四、将新“数据库”授权给新创键MySQL账户

GRANT ALL PRIVILEGES ON  `{$db_name}` . * TO  '{$db_user}'@'%' WITH GRANT OPTION ;

注意：Mysql5.6以后，第三、四（授权）步骤改为：

GRANT ALL PRIVILEGES ON  `{$db_name}` . * TO  '{$db_user}'@'%' IDENTIFIED BY '{$db_password}' WITH GRANT OPTION ;

存储函数

--
-- 创建ID表
--
CREATE TABLE IF NOT EXISTS `ppos_seq` (
  `seq_name` varchar(100) NOT NULL,
  `seq_val` int(10) unsigned NOT NULL,
  PRIMARY KEY (`seq_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--
-- 创建存储函数：每次调用函数时，自增1
--

delimiter //
CREATE FUNCTION `seq`(`select_name` CHAR(100)) RETURNS int(11)
    DETERMINISTIC
BEGIN
    UPDATE ppos_seq SET seq_val=last_insert_id(seq_val+1) WHERE seq_name=select_name;
    RETURN last_insert_id();
END //
delimiter ;

下载mysql5.6.tar.gz包，解压后重命名为mysql，并移到合适的位置。

例如我放的位置为/data/service/mysql，下面就以此为基础，安装mysql

一、初始化mysql，设置用户及权限

cd /data/service/mysql
sudo chown -R mysql:mysql ./
sudo scripts/mysql_install_db --user=mysql
...
...//内容略
...
sudo chown -R root .
sudo chown -R mysql data

……