MySQL数据表生成Wiki格式数据字典

<?php
$dbms='mysql';     //数据库类型
$host='localhost'; //数据库主机名
$dbName='joyous';    //使用的数据库
$user='test';      //数据库连接用户名
$pass='123456';          //对应的密码
$dsn="$dbms:host=$host;dbname=$dbName";

$table = array(
    't_user' => '用户表',
    't_user_info' => '用户扩展信息表',
 
);
try {
    $dbh = new PDO($dsn, $user, $pass); //初始化一个PDO对象
    $dbh->query('set names utf8');
    foreach ($table as $k => $v) {
        echo "### {$k}({$v})\n\n";
        $res = $dbh->query("SHOW FULL FIELDS FROM {$k}");
        $res->execute();
        $result = $res->fetchAll();
        echo "| 参数名称 | 类型 | 非空约束 | 备注说明|\n| ------ | ------ | ------ | ------ |\n";
        foreach ($result as $row)
        {
            echo "|{$row[0]}|{$row[1]}|{$row[3]}|{$row[8]}|\n";
        }
        echo "\n\n";
    }
} catch (PDOException $e) {
    echo $e->getMessage();
}

MySQL最大连接数和当前并发数

1.查看当前连接数和并发
执行:show status like ‘Threads%’;
+——————-+——-+
| Variable_name     | Value |
+——————-+——-+
| Threads_cached    | 220   |
| Threads_connected | 16    |
| Threads_created   | 236   |
| Threads_running   | 3     |
+——————-+——-+

Threads_connected 跟show processlist结果相同,表示当前连接数,Threads_running是代表当前并发

2.查看最大连接数
执行:show variables like ‘%max_connections%’;
+—————–+——-+
| Variable_name   | Value |
+—————–+——-+
| max_connections | 1200  |
+—————–+——-+

MYSQL死锁问题

如果insert或update用到了同样的索引也会造成死锁
比如
update table_a set name = ‘test1’ where id = 300; //用到了table_a的id主键索引
insert into table_b (a_id,`name`) (select id,name from table_a where id >=200)
ON DUPLICATE KEY UPDATE a_id = values(a_id)//同样用到了table_a的id主键
所以会导致死锁

行级锁并不是直接锁记录,而是锁索引,如果一条SQL语句用到了主键索引,mysql会锁住主键索引;如果一条语句操作了非主键索引,mysql会先锁住非主键索引,再锁定主键索引。

排查可以执行如下命令查看

show engine innodb status

 

MySQL主从故障解决

Slave_SQL_Running: No解决

1、在从数据库执行slave stop,停掉同步
2、查看主数据库状态
File: mysql-bin.000003
Position: 1151
3、在从数据库上执行
change master to master_host=’10.200.11.224′,master_user=’slave_test’, master_password=’123456′, master_port=3306, master_log_file=’mysql-bin.000003′, master_log_pos=1151;

Slave_IO_Running:connecting 解决办法
1、先确认下复制用户是否能连接到Master数据
2、查看设定的password、port、file以及pos是否正确

NodeJS操作Mysql示例

//config.js
module.exports={
	
	mysql:{
		host:'localhost',
		port:3306,
		user:'Joyous',
		password:'123456',
		database:'html5',
		charset:'utf8',
		connectionLimit:100,
	}
}
//mysql.js
var mysql=require('mysql');
var config = require('./config.js');
var DB  = mysql.createPool(config.mysql);
var DB_MYSQL = function(){
	__constructor();
	//数据查询接口
	this.fetchRow = function(tableName, idJson, callback) {
		DB.query('SELECT * FROM ' + tableName + ' where ? ', idJson, function(error, results){
			if(error) {
				console.log('ERROR :' + error.message);
				DB.end();
				callback(false);
			} else {
				if(results) {
					callback(results.pop());
				} else {
					callback(results);
				}
			}
		});
		
	};
	//数据插入接口
	this.insert = function(tableName, rowInfo, callback) {
		DB.query('INSERT INTO ' + tableName + ' SET ?', rowInfo, function(err, result){
			if(err) throw err;
			callback(result.insertId);
		});
		
	};
	//数据修改接口
	this.update = function(tableNmae, idJson, rowInfo, callback) {
		DB.query('UPDATE ' + tableName + ' SET ? where ?', [rowInfo, idJson], function(error, result){
			if(error) {
				console.log('UPDATE ERROR :' + error.message);
				callback(false);
			} else {
				callback(result)
			}
		});
	};
	//数据删除接口
	this.remove = function(tableName, idJson, callback){
		DB.query('DELETE ' + tableName + ' where ? ', idJson, function(err, results){
			if(err) {
				console.log('DELETE ERROR :' + err.message);
				DB.end();
				callback(false);
			} else {
				callback(true);
			}
		});
	};
	//自定义查询
	this.queryStr = function(sql,callback){
		DB.query(sql, function(error, result){
			if(error) {
				console.log('QUERY'+ error.message);
				DB.end();
				callback(false);
			} else {
				callback(result);
			}
		});
	};
	//条件查询
	this.fetchAll = function(tableName, whereJson, orderByjson, limitArr, selectStr, callback){
		var andWhere=whereJson['and'],orWhere=whereJson['or'],andArr=[],orArr=[];
		for(var i =  0;i0 ? andArr.join(' and ') : '',
			orStr = orArr.length > 0 ? orArr.join(' or ') : '',
			limitStr = limitArr.length > 0 ? ' limit '+limitArr.join(",") : "",
			orderStr = orderByjson['type'] ? ' order by ' + orderByjson['key'] + ' ' + orderByjson['type'] : '';
			var sql = 'SELECT ' + selectStr + ' FROM ' + tableName + ' where ' + andStr + orStr + orderStr + limitStr;
			DB.query(sql,function(err, results) {
				if(err) {
					console.log('FETCHALL ERROR :' + err.message);
					DB.end();
					callback(false);
				} else {
					callback(results);
				}
			});
	};
	//构造函数
	function __constructor(){

	}
};
module.exports = new DB_MYSQL();
//test.js
var DB=require('./lib/mysql.js');
DB.fetchRow('cj_sys',{id:1},function(res){
	console.log(res.id);
});

 

触发器统计记录行数

a表保存记录,b表保存a表当中指定cid的总行数
CREATE TABLE `a` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `cid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;

CREATE TRIGGER `insert_exec` AFTER INSERT ON `a` FOR EACH ROW begin
declare b_id int;
select id into b_id from b where cid = new.cid;
if  b_id  then
update b set num=num+1 where cid=new.cid;
else
insert into b (cid,num) values (new.cid, 1);
end if;
end;

CREATE TRIGGER `delete_exec` BEFORE DELETE ON `a` FOR EACH ROW update b  set num = num - 1 where cid= old.cid;


CREATE TABLE `b` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `cid` int(11) NOT NULL,
  `num` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;


 

MySQL配置主从复制

主数据库
10.200.11.224
从数据库
10.200.11.230
1、在主数据库添加用户
添加用户
GRANT REPLICATION SLAVE ON *.* TO ‘slave_test’@’10.200.11.%’ IDENTIFIED BY ‘123456’;
#添加用户slave_test并授权
查看是否添加成功
使用该账号在从库机器上登录一次试试
mysql -h 10.200.11.224 -u slave_test -p
如果不能登录需要将/etc/mysql/my.cnf当中bind-address= 127.0.0.1行屏蔽
2、
修改主数据库配置/etc/mysql/my.cnf(修改之前记得备份)
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#配置数据库复制
binlog_do_db = test_a
binlog_do_db = test_b
#配置数据库不做复制
binlog_ignore_db = information_schema
binlog_ignore_db = mysql
binlog_ignore_db = performance_schema

3、查看主数据库状态
mysql>show master status \G;
File: mysql-bin.000001
Position: 107
Binlog_Do_DB: test_a,test_b
Binlog_Ignore_DB: information_schema,mysql,performance_schema

4、修改从库配置/etc/mysql/my.cnf
server-id = 2
replicate-do-db = test_a
replicate-do-db = test_b
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
#5.1.7之后要在MYSQL命令行执行
change master to master_host=’10.200.11.224′, master_user=’slave_test’, master_password=’123456′,master_port=3306,master_log_file=’mysql-bin.0000011′,master_log_pos=107;
stop slave;
reset slave;
start slave;

备注:
主服务器查看
show processlist;
show master status \G;
从服务器查看
start slave;
show slave status \G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
说明主从已正常工作了

MYSQL主从同步

1、解决问题

数据分布不同节点、负载均衡、读写分离、容灾备份、高可用应用、故障切换等

2、同步原理

Master将操作记录到bin-log

salve的一个线程去Master读取bin-log

上面的线程结尾工作会把它们保存到relay-log中

salve另外线程去重复relay-log

参考

http://my.oschina.net/zijian1315/blog/202599

http://blog.csdn.net/qmhball/article/details/8233769

http://www.jb51.net/article/27222.htm

http://blog.csdn.net/hguisu/article/details/7325124

bash连接mysql数据库

#!/bin/bash
HOST='127.0.0.1'
PORT=3306
USERNAME='test'
PASSWORD='test'
DB='test'
#读取字段总数
FIELDNUM=5
select_sql='select * from test'
list=(`/opt/mysql/bin/mysql -h ${HOSTNAME} -u ${USERNAME} -P ${PORT} -p${PASSWORD} -e "use ${DB};${select_sql}" -N -s`)
#总数
total=${#list[@]}
#总行数
line=`expr ${total} / ${FIELDNUM}`
for((i=0;i<${line};i++));do
    j=$[$i * $FIELDNUM]
    echo ${list[j+0]}-${list[j+1]};//输出第一个字段和第二个字段
done;

备注:myql要写绝对路径,不然脚本可执行但是放到crontab就无法执行了!

ubuntu安装puref-ftpd

1.安装pureftpd

sudo apt-get install pure-ftpd

sudo apt-get install pure-ftpd-mysql

2.创建数据库和存放ftp用户表

CREATE TABLE `users` (
CREATE TABLE `users` (
  `User` varchar(16) NOT NULL DEFAULT '' COMMENT '账号',
  `Password` varchar(64) NOT NULL DEFAULT '' COMMENT '密码',
  `Uid` mediumint(8) NOT NULL DEFAULT '33' COMMENT '用户UID',
  `Gid` mediumint(8) NOT NULL DEFAULT '33' COMMENT '用户GID',
  `Dir` varchar(128) NOT NULL DEFAULT '' COMMENT '用户根目录',
  `Comment` tinytext NOT NULL COMMENT '备注',
  PRIMARY KEY (`User`),
  UNIQUE KEY `User` (`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='FTP用户';

 

3.配置
编辑/etc/pure-ftpd/db/mysql.conf
#MYSQLServer localhost
#MYSQLPort 3306
MYSQLUser pureftpd
MYSQLPassword ftpdpass
MYSQLDatabase pureftpd
#MYSQLCrypt md5, cleartext, crypt() or password() – md5 is VERY RECOMMENDABLE uppon cleartext
MYSQLCrypt md5

4.一些配置
//限制用户在自己家目录
echo “yes” >/etc/pure-ftpd/conf/ChrootEveryone
echo “no” >/etc/pure-ftpd/conf/AnonymousOnly

备注:

1、提示Can’t login as [XXXX]: account disabled

需要检查启动的时候u参数是多少

usr/sbin/pure-ftpd-mysql -l mysql:/etc/pure-ftpd/db/mysql.conf -l pam -H -8 UTF-8 -C 5 -O clf:/var/log/pure-ftpd/transfer.log -c 10 -u 1000 -E -B
这个-u就是最小的uid!
解决这个错误
echo ’33’ > /etc/pure-ftpd/conf/MinUID
那么最小的UID就是33!!!!
2、设置Umask要放到conf下Umask文件并且要用空格分开!!!
比如002 002就是设定文件是775和文件夹775一样的效果!!!!