[client]
port = 3306
socket = “D:/mysql/mysql.sock”

[mysqld]
port= 3306
socket = “D:/mysql/mysql.sock”
basedir = “D:/mysql”
tmpdir = “D:/tmp”
datadir = “D:/mysql/data”
pid_file = “mysql.pid”
key_buffer = 64M
max_allowed_packet = 512M
sort_buffer_size = 5120K
net_buffer_length = 256K
read_buffer_size = 1024K
read_rnd_buffer_size = 5120K
myisam_sort_buffer_size = 256M
log_error = “mysql_error.log”
lower_case_table_names=1

plugin_dir = “D:/mysql/lib/plugin/”

server-id = 1

innodb_data_home_dir = “D:/mysql/data”
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = “D:/mysql/data”
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 32M

innodb_log_file_size = 640M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
wait_timeout = 2880000
interactive_timeout = 2880000

[mysqldump]
quick
max_allowed_packet = 512M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 200M
sort_buffer_size = 200M
read_buffer = 32M
write_buffer = 32M

[myisamchk]
key_buffer = 200M
sort_buffer_size = 200M
read_buffer = 32M
write_buffer = 32M

[mysqlhotcopy]
interactive-timeout

centos7 安装双mysql

之前安装了mysql的5.6.19 现在继续安装此版本主要用于主从复制

1.安装cmake (网上自行下载mysql5.6.19)

wget http://www.cmake.org/files/v2.8/cmake-2.8.10.2.tar.gz
tar -xzvf cmake-2.8.10.2.tar.gz
cd cmake-2.8.10.2
./bootstrap
make && make install

安装一下工具

yum -y install wget gcc-c++ ncurses ncurses-devel cmake make perl bison openssl openssl-devel gcc* libxml2 libxml2-devel curl-devel libjpeg* libpng* freetype*

cd mysql-5.6.16

安装mysql

cmake \
-DCMAKE_INSTALL_PREFIX=/data/mysql3307 \
-DMYSQL_UNIX_ADDR=/data/mysql3307/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/data/mysql3307/data \
-DMYSQL_TCP_PORT=3307 \
-DENABLE_DOWNLOADS=1

初始化mysql

scripts/mysql_install_db –basedir=/usr/local/mysql3307/ –user=mysql

修改mysql的配置

vi /usr/local/mysql3307/etc/my.cnf

[client]
port = 3307 –数据库端口号
socket = /data/mysql3307/mysql.sock –sock文件路径
default-character-set=utf8 –客户端UTF8连接
[mysqld]
port = 3307 –数据库端口号
socket = /data/mysql3307/mysql.sock –sock文件路径

安装mysql的系统服务

support-files/mysql.server /etc/init.d/mysql3307

vi /etc/init.d/mysql3307

basedir=/data/mysql3307
datadir=/data/mysql3307/data
mysqld_pid_file_path=/data/mysql3307/mysql11.pid

保存成功后试试

/etc/init.d/mysql3307 start

centos7本机安装双mysql教程

MySQL主从复制

1、简介
我们为什么要用主从复制?主从复制目的:
可以做数据库的实时备份,保证数据的完整性;
可做读写分离,主服务器只管写,从服务器只管读,这样可以提升整体性能。

下图是MySQL官方给出了使用Replication的场景:


原理图:

2、VMware安装 CentOS-7-x86_64-DVD-1511.iso 此步骤略过
暂时分配2G空间,等所有都安装完毕就改成1G (为了安装软件快速 电脑内存8G) 先装两台,在装一台
分配IP为 192.168.0.220、192.168.0.221、192.168.0.222 简单起见root 和 myql密码 都为123456
安装成功后安装两个软件 yum install wget #获取软件 yum install net-tools #ifconfig 工具
3、安装mysql 并修改密码(centos7删除了MySQL,所以我们找到其他源)
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum install mysql-server
service mysqld restart
mysql -u root
use mysql; update user set password=password(‘123456′) where user=’root';
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘123456’ WITH GRANT OPTION; — 配置外网访问
FLUSH PRIVILEGES;

Read More →

某张表数据量很大,但只需保留最近3个月的数据,直接删数据又怕客户找麻烦,所以就想创建一张备用表,将3个月以前的数据挪到备用表中。
环境
CentOS 6.7 32bit
MySQL 5.6.X
操作

# 创建归档表(只复制表结构和索引)
CREATE TABLE MyTableBak like MyTable;

# 删除归档表的所有索引(这样可以提高插入速度)

# 将3个月以前的数据导入归档表
INSERT INTO MyTableBak SELECT * FROM MyTable WHERE date < ‘2015-12-25′;

# 删除原表3个月以前的数据
DELETE FROM MyTable where date < ‘2015-12-25′

来源:http://yestreenstars.blog.51cto.com/1836303/1744832/

MySQL报错 Lock wait timeout exceeded; try restarting transaction
1,查看数据库的隔离级别:

mysql> select @@tx_isolation;
+—————–+
| @@tx_isolation |
+—————–+
| READ-COMMITTED |
+—————–+
1 row in set (0.00 sec)
2,去查看先当前库的线程情况:
mysql> show full processlist;

没有看到正在执行的慢SQL记录线程,再去查看innodb的事务表INNODB_TRX,看下里面是否有正在锁定的事务线程,看看ID是否在show full processlist里面的sleep线程中,如果是,就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了,我们需要手动kill掉。

mysql> SELECT * FROM information_schema.INNODB_TRX;

3,看到有两条记录,然后找到 trx_mysql_thread_id
mysql> SELECT trx_mysql_thread_id FROM information_schema.INNODB_TRX;

mysql> kill 7226287;
Query OK, 0 rows affected (0.00 sec)

mysql> kill 5201247;
Query OK, 0 rows affected (0.00 sec)

然后再去查询INNODB_TRX表,就没有阻塞的事务sleep线程存在了,如下所示:
mysql> SELECT * FROM information_schema.INNODB_TRX;

再去执行update语句,就能正常执行了,如下所示:
4,总结分析
表数据量也不大,按照普通的情况来说,简单的update应该不会造成阻塞的,mysql都是autocommit,不会出现update卡住的情况,去查看下autocommit的值。
mysql> select @@autocommit;
+————–+
| @@autocommit |
+————–+
| 1 |
+————–+
1 row in set (0.00 sec)

如果是0的话 就设置为1 set global autocommit=1;