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  |
+—————–+——-+

存储过程

## 优点
1. 更好性能
 * 存储过是预编译的,在创建时编译;一般SQL是每次执行都会编译

2. 功能实现更加灵活
 * 存储过程可以使用条件判断和游标,有很强的灵活性,还可以使用数据库内置函数,完成复杂的判断和运算
3. 减少网络传输
 * 复杂的多条SQL操作会消耗大量的网络传输,多条SQL存入存储过程会降低网络负载
4. 安全性
 * 存储过程可以屏蔽对底层数据库对象的直接访问
 * 执行过程的调用是可见的,无法看到表结构和数据库,不能嵌入SQL,有助于避免SQL注入
 

缺点
1. 架构不清晰,不够面向对象
2. 开发和维护要求比较高
3. 可移植性差

选择
1. 事务涉及多个SQL语句或者操作多个表可以选择使用存储过程
2. 事务完成很复杂的逻辑可以考虑使用存储过程
3. 比较复杂的统计或汇总可以应用存储过程

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);
});

 

Centos7安装LAMP+PHPmyadmin

1、更新Centos7下载源
更新之前记得备份!!!
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
参考:http://mirrors.aliyun.com/help/centos
163的源参考:http://mirrors.163.com/.help/centos.html
2、更新本地缓存
yum clean all
yum makecache
3、安装网络工具
yum install net-tools
如果你要用ifconfig等命令的话
4、关闭firewall
systemctl stop firewalld.service
systemctl disable firewalld.service
5、关闭selinux
vi /etc/selinux/config
注释#SELINUX=enforcing
注释#SELINUXTYPE=targeted
添加SELINUX=disabled
保存退出:wq
setenforce 0#关闭selinux
6、安装apache
yum install httpd
7、安装Mariadb
yum install mariadb mariadb-server
拷贝配置文件覆盖原配置文件
cp /usr/share/mysql/my-huge.cnf /etc/my.cnf
8、安装PHP
yum install php php-mysql php-gd php-odbc php-pear php-mbstring php-mcrypt
9、下载PHPmyadmin
wget https://files.phpmyadmin.net/phpMyAdmin/4.0.0/phpMyAdmin-4.0.0-all-languages.zip
解压到/var/www/html/phpmyadmin

10、配置站点
vi /etc/httpd/conf/httpd.conf
文件最后加入

Include vhost/*.conf
mkdir /etc/httpd/vhost
touch test.conf

DocumentRoot /var/www/html/test
ServerName test

Options FollowSymLinks
AllowOverride All


touch phpmyadmin.conf

DocumentRoot /var/www/html/phpmyadmin
ServerName phpmyadmin

Options FollowSymLinks
AllowOverride All


重启apache和mariadb
11、安装epel源
wget http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-8.noarch.rpm
如果版本不一样就去这里找http://dl.fedoraproject.org/pub/epel/
rpm -ivh rpm epel-release-7-8.noarch.rpm
yum clean all
yum update
以下步骤都是在安装了epel源下进行的
12、安装mcrypt扩展
yum install php-mcrypt
13、安装redis、memcache以及扩展
yum install redis
vi /etc/redis.conf
daemonize yes#守护进程运行
redis-server /etc/redis.conf
yum install php-redis
yum install memcached
yum install php-memcached

附录:
启动|停止|重启apache systemctl start|stop|restart httpd.service
开机启动apache systemctl enable httpd.service
启动|停止|重启mariadb systemctl start|stop|restart mariadb.service
开启启动mariadb systemctl enable mariadb.service

触发器统计记录行数

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