命令刷新SQL server 中 数据库:
你提交正常的数据更新后，SQL server 中 数据库，也会自动更新，在你想刷新的时候，重新读取加载一次即可刷新，不需要你额外做什么。
Ⅱ SQL数据磁盘满了怎么解决
-- 清空日志
--压缩日志及数据库文件大小
/*--特别注意
请按步骤进行,未进行前面的步骤,请不要做后面的步骤
否则可能损坏你的数据库.
--*/
select*fromsysfiles
--1.清空日志
DUMPTRANSACTIONusernameWITHNO_LOG
--2.截断事务日志：
BACKUPLOGusernameWITHNO_LOG
--3.收缩数据库文件(如果不压缩,数据库的文件不会减小
-- 企业管理器--右键你要压缩的数据库--所有任务--收缩数据库--收缩文件
--选择日志文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了
--选择数据文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了
-- 也可以用SQL语句来完成
--收缩数据库
DBCCSHRINKDATABASE(username)
--收缩指定数据文件,1是文件号,可以通过这个语句查询到:select*fromsysfiles
DBCCSHRINKFILE(2)
--4.为了最大化的缩小日志文件(如果是sql7.0,这步只能在查询分析器中进行)
-- a.分离数据库:
-- 企业管理器--服务器--数据库--右键--分离数据库
-- b.在我的电脑中删除LOG文件
-- c.附加数据库:
-- 企业管理器--服务器--数据库--右键--附加数据库
-- 此法将生成新的LOG，大小只有500多K
-- 或用代码：
-- 下面的示例分离username，然后将username中的一个文件附加到当前服务器。
execsp_dboptionusername,'singleuser',true
a.分离
EXECsp_detach_db@dbname='username'
b.删除日志文件
execmaster..xp_cmdshell'delD:\ProgramFiles\SQL\database\username_LOG.ldf'
c.再附加
EXECsp_attach_single_file_db@dbname='username',
@physname='D:\ProgramFiles\SQL\database\username_Data.MDF'
--5.为了以后能自动收缩,做如下设置:
-- 企业管理器--服务器--右键数据库--属性--选项--选择"自动收缩"
--SQL语句设置方式:
EXECsp_dboption'数据库名','autoshrink','TRUE'
--6.如果想以后不让它日志增长得太大
-- 企业管理器--服务器--右键数据库--属性--事务日志
--将文件增长限制为xM(x是你允许的最大数据文件大小)
--SQL语句的设置方式:
alterdatabase数据库名modifyfile(name=逻辑文件名,maxsize=20)
Ⅲ sql server 临时表占用硬盘吗
我们仍然使用两个会话，一个会话 run，用于运行主 SQL；另一个会话 ps，用于进行 performance_schema 的观察：
主会话线程号为 29，
可以看到写入的线程是 page_clean_thread，是一个刷脏操作，这样就能理解数据为什么是慢慢写入的。
也可以看到每个 IO 操作的大小是 16K，也就是刷数据页的操作。
结论：
我们可以看到，
1. MySQL 会基本遵守 max_heap_table_size 的设定，在内存不够用时，直接将表转到磁盘上存储。
2. 由于引擎不同（内存中表引擎为 heap，磁盘中表引擎则跟随 internal_tmp_disk_storage_engine 的配置），本次实验写磁盘的数据量和实验 05中使用内存的数据量不同。
3. 如果临时表要使用磁盘，表引擎配置为 InnoDB，那么即使临时表在一个时间很短的 SQL 中使用，且使用后即释放，释放后也会刷脏页到磁盘中，消耗部分 IO。
Ⅳ 如何远程备份MySQL binlog
用脚本对远程服务器进行备份的方式，有个缺点：无法对MySQL服务器当前正在写的二进制日志文件进行备份。所以，只能等到MySQL服务器全部写完才能进行备份。而写完一个binlog的时间并不固定，这就导致备份周期的不确定。
从MySQL5.6开始，mysqlbinlog支持将远程服务器上的binlog实时复制到本地服务器上。
mysqlbinlog的实时二进制复制功能并非简单的将远程服务器的日志复制过来，它是通过MySQL 5.6公布的Replication API实时获取二进制事件。本质上，就相当于MySQL的从服务器。与普通服务器类似，主服务器发生事件后，一般都会在0.5~1秒内进行备份。
备份命令
mysqlbinlog--read-from-remote-server--raw--host=192.168.244.145--port=3306--user=repl--password=repl--stop-nevermysql-bin.000001
解释如下：
--read-from-remote-server：用于备份远程服务器的binlog。如果不指定该选项，则会查找本地的binlog。
--raw：binlog日志会以二进制格式存储在磁盘中，如果不指定该选项，则会以文本形式保存。
--user：复制的MySQL用户，只需要授予REPLICATION SLAVE权限。
--stop-never：mysqlbinlog可以只从远程服务器获取指定的几个binlog，也可将不断生成的binlog保存到本地。指定此选项，代表只要远程服务器不关闭或者连接未断开，mysqlbinlog就会不断的复制远程服务器上的binlog。
mysql-bin.000001：代表从哪个binlog开始复制。
除了以上选项外，还有以下几个选项需要注意：
--stop-never-slave-server-id：在备份远程服务器的binlog时，mysqlbinlog本质上就相当于一个从服务器，该选项就是用来指定从服务器的server-id的。默认为-1。
--to-last-log：代表mysqlbinlog不仅能够获取指定的binlog，还能获取其后生成的binlog，获取完了，才终止。如果指定了--stop-never选项则会隐式打开--to-last-log选项。
--result-file：用于设置远程服务器的binlog，保存到本地的前缀。譬如对于mysql-bin.000001，如果指定--result-file=/test/backup-，则保存到本地后的文件名为/test/backup-mysql-bin.000001。注意：如果将--result-file设置为目录，则一定要带上目录分隔符“/”。譬如--result-file=/test/，而不是--result-file=/test，不然保存到本地的文件名为/testmysql-bin.000001。
不足：
这个方式有个问题，对于常规的主从复制来说，如果主从直接的连接断开了，则从会自动再次连接，而对于mysqlbinlog，如果断开了，并不会自动连接。
解决方案：
可通过脚本来弥补上述不足
#!/bin/sh
BACKUP_BIN=/usr/bin/mysqlbinlog
LOCAL_BACKUP_DIR=/backup/binlog/
BACKUP_LOG=/backup/binlog/backuplog
REMOTE_HOST=192.168.244.145
REMOTE_PORT=3306
REMOTE_USER=repl
REMOTE_PASS=repl
FIRST_BINLOG=mysql-bin.000001
#
SLEEP_SECONDS=10
##createlocal_backup_dirifnecessary
mkdir-p${LOCAL_BACKUP_DIR}
cd${LOCAL_BACKUP_DIR}
##运行while循环，连接断开后等待指定时间，重新连接
while:
do
if[`ls-A"${LOCAL_BACKUP_DIR}"|wc-l`-eq0];then
LAST_FILE=${FIRST_BINLOG}
else
LAST_FILE=`ls-l${LOCAL_BACKUP_DIR}|grep-vbackuplog|tail-n1|awk'{print$9}'`
fi
${BACKUP_BIN}--raw--read-from-remote-server--stop-never--host=${REMOTE_HOST}--port=${REMOTE_PORT}--user=${REMOTE_USER}--password=${REMOTE_PASS}${LAST_FILE}
echo"`date+"%Y/%m/%d%H:%M:%S"`mysqlbinlog停止，返回代码：$?"|tee-a${BACKUP_LOG}
echo"${SLEEP_SECONDS}秒后再次连接并继续备份"|tee-a${BACKUP_LOG}
sleep${SLEEP_SECONDS}
done
脚本解读：
1. 实际上定义了一个死循环，如果备份失败，则10s后重新连接。
2. 第一次运行时需指定FIRST_BINLOG的值，指从哪个binlog开始复制，一般为mysql-bin.000001。后续执行的时候就直接获取备份目录下最新的binlog，从最新的binlog开始复制。
总结：
1. 如果指定了--raw，mysqlbinlog获取事件后，并不会实时落盘，而是先保存在本地服务器的内存中，每4K刷盘一次。这也就减少了频繁的日志写操作。如果此时mysqlbinlog和主服务器之间的连接断开了，则内存中的binlog会马上刷新到磁盘中。
2. 尽管mysqlbinlog类似于从服务器，但从服务器上的relaylog却是实时存盘的，即从服务器获取主服务器产生的事件后，会实时写入到relaylog中。
3. 如果不指定--raw，这个时候会以文本格式存盘，此时，--result-file=/test/不能指定为目录，必须明确写上文件名，譬如--result-file=/test/1.sql，此时，mysqlbinlog获取事件后，是实时落盘的，不会每4K刷盘一次。
Ⅳ SQL可以安装在其他盘上吗
没有啊，我就安装在d盘了啊，可以选择的啊，但是在xp下好像是有一个版本是不能装的，企业版或者是个人版忘了。
Ⅵ sql刷选数据库
SELECT * FROM TABLE WHERE X>X1 AND X<X2 AND Y>Y1 AND Y<Y2
Ⅶ mysql插入1000条数据到数据表中如何能加快速度
常用的插入语句如：
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('1', 'userid_1', 'content_1', 1);
修改成：
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);
修改后的插入操作能够提高程序的插入效率。这里第二种SQL执行效率高的主要原因是合并后日志量（MySQL的binlog和innodb的事务让日志）减少了，降低日志刷盘的数据量和频率，从而提高效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的IO。
SQL语句是有长度限制，在进行数据合并在同一SQL中务必不能超过SQL长度限制，通过max_allowed_packet配置可以修改，默认是1M，测试时修改为8M。
Ⅷ 如何优化MySQL insert性能
测试场景
MySQL 5.7.12
主要测试 不同刷盘参数 对性能的影响, 使用以下三个场景:
sync_binlog=1, innodb_flush_log_at_trx_commit=1, 简写为b1e1 (binlog-1-engine-1)
sync_binlog=0, innodb_flush_log_at_trx_commit=1, 简写为b0e1
sync_binlog=0, innodb_flush_log_at_trx_commit=0, 简写为b0e0
- mysql -h127.0.0.1 -P5712 -uroot -pmsandbox -e"truncate table test.sbtest1";
- /opt/sysbench-0.5/dist/bin/sysbench --test=/opt/sysbench-0.5/dist/db/insert.lua --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=msandbox --mysql-port=5712 --oltp-table-size=1000000 --mysql-db=test --oltp-table-name=stest --num-threads=1 --max-time=30 --max-requests=1000000000 --oltp-auto-inc=off --db-driver=mysql run
- global tids
- probe process("/home/huangyan/sandboxes/5.7.12/bin/mysqld").function("mysql_execute_command") {
- if (pid() == target() && tids[tid()] == 0) {
- tids[tid()] = 1;
- }
- }
- global oncpu
- probe timer.profile {
- if (tids[tid()] == 1) {
- oncpu[ubacktrace()] <<< 1;
- }
- }
- probe timer.s(10) {
- exit();
- }
- probe end {
- foreach (i in oncpu+) {
- print_stack(i);
- printf(" %d ", @count(oncpu[i]));
- }
- }
在b0e1场景中,_ZL27log_write_flush_to_disk_lowv的占比是12.93%, 其绝大部分时间是用于将innodb的log刷盘.
在b0e0场景中,_ZL27log_write_flush_to_disk_lowv的开销被节省掉, 理论上的事务数比例应是1-12.93%=87.07%, 实际事务数的比例是119274/166424=71.67%, 误差较大
- global tids
- probe process("/home/huangyan/sandboxes/5.7.12/bin/mysqld").function("mysql_execute_command") {
- if (pid() == target() && tids[tid()] == 0) {
- tids[tid()] = 1;
- }
- }
- global oncpu, offcpu, timer_count, first_cpu_id = -1;
- probe timer.profile {
- if (first_cpu_id == -1) {
- first_cpu_id = cpu();
- }
- if (tids[tid()] == 1) {
- oncpu[ubacktrace()] <<< 1;
- }
- if (first_cpu_id == cpu()) {
- timer_count++;
- }
- }
- global switchout_ustack, switchout_timestamp
- probe scheler.ctxswitch {
- if (tids[prev_tid] == 1) {
- switchout_ustack[prev_tid] = ubacktrace();
- switchout_timestamp[prev_tid] = timer_count;
- }
- if (tids[next_tid] == 1 && switchout_ustack[next_tid] != "") {
- offcpu[switchout_ustack[next_tid]] <<< timer_count - switchout_timestamp[next_tid];
- switchout_ustack[next_tid] = "";
- }
- }
- probe timer.s(10) {
- exit();
- }
- probe end {
- foreach (i in oncpu+) {
- print_stack(i);
- printf(" %d ", @sum(oncpu[i]));
- }
- foreach (i in offcpu+) {
- printf("---");
- print_stack(i);
- printf(" %d ", @sum(offcpu[i]));
- }
- }
- Profiling timers are available to provide probes that execute on all CPUs at each system tick.
- if (first_cpu_id == cpu()) {
- timer_count++;
- }
修改stap脚本, 在b1e1中不统计sync_binlog的代价. 生成的火焰图表示消除sync_binlog代价后, 理论上的服务器压力类型.
与b0e1产生的火焰图做比较.
- probe end {
- foreach (i in oncpu+) {
- if (isinstr(sprint_stack(i), "my_sync")) {
- continue;
- }
- print_stack(i);
- printf(" %d ", @sum(oncpu[i]));
- }
- foreach (i in offcpu+) {
- if (isinstr(sprint_stack(i), "my_sync")) {
- continue;
- }
- printf("---");
- print_stack(i);
- printf(" %d ", @sum(offcpu[i]));
- }
- }
- global ctxswitch_times
- probe scheler.ctxswitch {
- ctxswitch_times++;
- ...
- }
- probe end {
- ...
- printf("ctxswitch_times=%d ", ctxswitch_times);
- }
利用火焰图, 可以快速诊断出MySQL服务器级别的性能瓶颈, 做出合理的参数调整
对于IO类型的操作的观测, 需要考虑oncpu和offcpu两种情况
由于观测手段中使用了上下文切换作为观测点, 那IO操作数量的不同, 会引起上下文切换次数的不同, 从而引起观测误差.
MySQL 环境搭建使用MySQL sandbox, 对应三个场景的启动参数如下:
1../start --sync-binlog=1 --log-bin=bin --server-id=5712 --gtid-mode=ON --enforce-gtid-consistency=1 --log-slave-updates=1
2../start --sync-binlog=0 --log-bin=bin --server-id=5712 --gtid-mode=ON --enforce-gtid-consistency=1 --log-slave-updates=1
3../start --sync-binlog=0 --log-bin=bin --server-id=5712 --gtid-mode=ON --enforce-gtid-consistency=1 --log-slave-updates=1 --innodb-flush-log-at-trx-commit=0
压力生成使用sysbench:
性能观测工具使用systemtap(简称stap), version 2.7/0.160
基准
在没有观测压力的情况下, 对三种场景分别进行基准测试, 用以矫正之后测试的误差:
场景
sysbench事务数
b1e1 67546
b0e1 125699
b0e0 181612
火焰图与offcpu
火焰图
火焰图是Brendan Gregg首创的表示性能的图形方式, 其可以直观的看到压力的分布. Brendan提供了丰富的工具生成火焰图.
火焰图比较b0e1和b0e0
使用stap脚本获取CPU profile, 并生成火焰图(火焰图生成的命令略, 参看Brendan的文档)
stap脚本
注意:
1. 脚本只抓取MySQL的用户线程的CPU profile, 不抓取后台进程.
2. 脚本只抓取10s, 相当于对整个sysbench的30s过程进行了短期抽样.
b0e1生成的火焰图
性能
在开启观测的情况下, 观察性能:
场景
sysbench事务数
b0e1 119274
b0e0 166424
分析
在生成的火焰图中, 可以看到:
误差较大的问题, 要引入offcpu来解决.
offcpu
在之前的分析中我们看到理论和实际的事务数误差较大. 考虑_ZL27log_write_flush_to_disk_lowv的主要操作是IO操作, IO操作开始, 进程就会被OS进行上下文切换换下台, 以等待IO操作结束, 那么只分析CPU profile就忽略了IO等待的时间, 也就是说_ZL27log_write_flush_to_disk_lowv的开销被低估了.
offcpu也是Brendan Gregg提出的概念. 对于IO操作的观测, 除了CPU profile(称为oncpu时间), 还需要观测其上下文切换的代价, 即offcpu时间.
修改一下stap脚本可以观测offcpu时间. 不过为了将oncpu和offcpu的时间显示在一张火焰图上作对比, 我对于Brendan的工具做了微量修改, 本文将不介绍这些修改.
stap脚本
注意:timer.profile的说明中是这样写的:
也就是说在一个时间片中,timer.profile会对每一个CPU调用一次. 因此代码中使用了如下代码, 保证时间片技术的正确性:
b0e1生成的带有offcpu的火焰图
性能
由于变更了观测脚本, 需要重新观测性能以减小误差:
场景
sysbench事务数
b0e1 105980
b0e0 164739
分析
在火焰图中, 可以看到:
1._ZL27log_write_flush_to_disk_lowv的占比为31.23%
2. 理论上的事务数比例应是1-31.23%=68.77%, 实际事务数的比例是105980/164739=64.33%, 误差较小.
观测误差的矫正
在比较b0e1和b0e0两个场景时, 获得了比较好的结果. 但同样的方法在比较b1e1和b0e1两个场景时, 出现了一些误差.
误差现象
b1e1的火焰图如图:
其中_ZN13MYSQL_BIN_LOG16sync_binlog_fileEb(sync_binlog的函数)占比为26.52%.
但性能差异为:
场景
sysbench事务数
b1e1 53752
b0e1 105980
理论的事务数比例为1-26.52%=73.48%, 实际事务数比例为53752/105980=50.71%, 误差较大.
分析压力分布
首先怀疑压力转移, 即当sync_binlog的压力消除后, 服务器压力被转移到了其它的瓶颈上. 但如果压力产生了转移, 那么实际事务数比例应大于理论事务数比例, 即sync_binlog=0带来的性能提升更小.
不过我们还是可以衡量一下压力分布, 看看b1e1和b0e1的压力有什么不同, 步骤如下:
stap脚本
只修改了probe end部分, 略过对my_sync堆栈的统计:
结果
b1e1, 理论的服务器压力图:
b0e1, 实际的服务器压力图:
可以看到, 压力分布是非常类似, 即没有发生压力分布.
BTW: 这两张图的类似, 具有一定随机性, 需要做多次试验才能产生这个结果.
分析
既然理论和实际的压力分布类似, 那么可能发生的就是压力的整体等比缩小. 推测是两个场景上的观测成本不同, 导致观测影响到了所有压力的观测.
观察stap脚本, 其中成本较大的是ctxswitch, 即上下文切换时的观测成本.
上下文切换的观测成本
如果 “上下文切换的观测成本 影响 场景观测 的公平性” 这一结论成立, 那么我们需要解释两个现象:
1. b1e1和b0e1的比较, 受到了 上下文切换的观测成本 的影响
2. b0e1和b0e0的比较, 未受到 上下文切换的观测成本 的影响
假设 上下文切换的观测成本 正比于 上下文切换的次数, 那么我们只需要:
1. 观测每个场景的上下文切换次数
2. 对于b1e1和b0e1的比较, 由上下文切换次数计算得到理论的降速比, 与实际的降速比进行比较
3. 对于b0e1和b0e0的比较, 由上下文切换次数计算得到是否会带来降速.
stap脚本
在probe scheler.ctxswitch和probe end增加了ctxswitch_times相关的内容:
结果
场景
sysbench事务数
上下文切换次数
sync_binlog占比
b1e1 55352 826370 36.80%
b0e1 105995 693383 –
b0e0 162709 675092 –
分析结果:
1. b1e1与b0e1的比较
1. 理论降速比:693383/826370 = 83.90%
2. 实际降速比:(实际的事务数比例/由sync_binlog占比推算的理论的事务数比例) = (55352/105995)/(1-36.80%) = 0.5222/0.6320 = 82.63%
3. 误差很小. 即b1e1与b0e1的比较中, 理论值和实际值的误差来自于: IO操作的减少导致上下文切换的数量减小, 使得两个场景的观察成本不同.
2. b0e1与b0e0的比较: 上下文切换次数相近, 即两个场景的观察成本相同.
实验结果符合之前的分析.
结论
Ⅸ 如何解决主从数据库同步延迟问题
最简单的减少slave同步延时的方案就是在架构上做优化，尽量让主库的DDL快速执行。还有就是主库是写，对数据安全性较高，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置，而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flushlog也可以设置为0来提高sql的执行效率。另外就是使用比主库更好的硬件设备作为slave。
mysql-5.6.3已经支持了多线程的主从复制。原理和丁奇的类似，丁奇的是以表做多线程，Oracle使用的是以数据库(schema)为单位做多线程，不同的库可以使用不同的复制线程。
sync_binlog=1
This makes MySQL synchronize the binary log’s contents to disk each time it commits a transaction
默认情况下，并不是每次写入时都将binlog与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃，有可能binlog中最后的语句丢 失了。要想防止这种情况，你可以使用sync_binlog全局变量(1是最安全的值，但也是最慢的)，使binlog在每N次binlog写入后与硬盘 同步。即使sync_binlog设置为1,出现崩溃时，也有可能表内容和binlog内容之间存在不一致性。如果使用InnoDB表，MySQL服务器 处理COMMIT语句，它将整个事务写入binlog并将事务提交到InnoDB中。如果在两次操作之间出现崩溃，重启时，事务被InnoDB回滚，但仍 然存在binlog中。可以用--innodb-safe-binlog选项来增加InnoDB表内容和binlog之间的一致性。(注释：在MySQL 5.1中不需要--innodb-safe-binlog；由于引入了XA事务支持，该选项作废了），该选项可以提供更大程度的安全，使每个事务的 binlog(sync_binlog =1)和(默认情况为真)InnoDB日志与硬盘同步，该选项的效果是崩溃后重启时，在滚回事务后，MySQL服务器从binlog剪切回滚的 InnoDB事务。这样可以确保binlog反馈InnoDB表的确切数据等，并使从服务器保持与主服务器保持同步(不接收 回滚的语句)。
innodb_flush_log_at_trx_commit （这个很管用）
抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电 池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬 盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。