分类目录归档:数据库

数据库管理员

13.2.9. TRUNCATE语法,删除大表,并释放空间

13.2.9. TRUNCATE语法
TRUNCATE [TABLE] tbl_name
TRUNCATE TABLE用于完全清空一个表。从逻辑上说,该语句与用于删除所有行的DELETE语句等同,但是在
有些情况下,两者在使用上有所不同。
对于InnoDB表,如果有需要引用表的外键限制,则TRUNCATE TABLE被映射到DELETE上;否则使用快速删减
(取消和重新创建表)。使用TRUNCATE TABLE重新设置AUTO_INCREMENT计数器,设置时不考虑是否有外
Chapter 13. SQL Statement Syntax
file:///D:/download/refman-5.1-zh.html-chapter/refman-5.1-zh.html-chapter/sql-syntax.html[2010/2/24 5:22:43]
键限制。
对于其它存储引擎,在MySQL 5.1中,TRUNCATE TABLE与DELETE FROM有以下几处不同:
· 删减操作会取消并重新创建表,这比一行一行的删除行要快很多。
· 删减操作不能保证对事务是安全的;在进行事务处理和表锁定的过程中尝试进行删减,会发生错误。
· 被删除的行的数目没有被返回。
· 只要表定义文件tbl_name.frm是合法的,则可以使用TRUNCATE TABLE把表重新创建为一个空表,即使
数据或索引文件已经被破坏。
· 表管理程序不记得最后被使用的AUTO_INCREMENT值,但是会从头开始计数。即使对
于MyISAM和InnoDB也是如此。MyISAM和InnoDB通常不再次使用序列值。
· 当被用于带分区的表时,TRUNCATE TABLE会保留分区;即,数据和索引文件被取消并重新创建,同时
分区定义(.par)文件不受影响。
TRUNCATE TABLE是在MySQL中采用的一个Oracle SQL扩展。

mysql查看表的大小

如果想知道MySQL数据库中每个表占用的空间、表记录的行数的话,可以打开MySQL的 information_schema 数据库。在该库中有一个 TABLES 表,这个表主要字段分别是:

TABLE_SCHEMA : 数据库名
TABLE_NAME:表名
ENGINE:所使用的存储引擎
TABLES_ROWS:记录数
DATA_LENGTH:数据大小
INDEX_LENGTH:索引大小

其他字段请参考MySQL的手册,我们只需要了解这几个就足够了。

所以要知道一个表占用空间的大小,那就相当于是 数据大小 + 索引大小 即可。

SQL:

SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS FROM TABLES WHERE TABLE_SCHEMA=’数据库名’ AND TABLE_NAME=’表名’

不停机做mysql主从

2.1:在A机器上mysqldump -uroot -p123 -E -R –master-data databasename>c:\0803.sql
得到备份文件
2.2:到B机器上还原mysql -uroot -p123 databasename<c:\0803.sql
2.3:用MORE命令,得到0803.SQL里的change master to 命令里的参数
2.4:在B里,STOP SLAVE,再CHANGE MASTER TO,再START SLAVE

g++: yes: No such file or directory mysql编译出错

configure 没问题

make时出错,最后有如下错误信息

g++: yes: No such file or directory

mysql官方说,configure时可以去掉–with-mysqld-libs,或者指定相关的路径什么的,我去掉后还是出错

后来去掉–with-named-curses-libs选项后,正常了

mysql的datadir中的文件用途说明,网上收集,仅供参考

mysql的datadir文件分析说明:
1、ibdata1
这个文件超级大, 查了一下, 大概的作用如下
是储存的格式
INNODB类型数据状态下,
ibdata用来储存文件的数据
而库名的文件夹里面的那些表文件只是结构而已

由于mysql4.1默认试innodb,所以这个文件默认就存在了http://man.chinaunix.net/database/mysql/inonodb_zh/2.htm  这个链接试innodb的中文参考, innodb的东西可以在my.ini中设置

2、ib_logfileX
The ib_logfileX files are log files used by the innodb storage engine
mainly to rollback any uncommitted transactions so that the database
is in a consistent state after it recovers from a crash
大概的意思是ib_logfilex文件是innodb存储引擎使用的日志文件,用来回滚一些未提交的sql,

3、localhost-relay-bin.005759和localhost-relay-bin.index
默认情况,中继日志使用host_name-relay-bin.nnnnnn形 式的文件名,其中host_name是从服务器主机名,nnnnnn是 序列号。用连续序列号来创建连续中继日志文件,从000001开始。从服务器跟踪索引文件中目前正 使用的中继日志。 默认中继日志索引文件名为host_name-relay-bin.index。 默认情况,在从服务器的数据目录中创建这些文件。可以用–relay-log和–relay-log-index服 务器选项覆盖 默认文件名。参见6.8节,“复制启动选项”。

中继日志与二进制日志的格式相同,并且可以用mysqlbinlog读取。SQL线 程执行完中继日志中的所有事件并且不再需要之后,立即自动删除它。没有直接的删除中继日志的机制,因为SQL线程可以负责完 成。然而,FLUSH LOGS可以循环中继日志,当SQL线程删除日志时会有影响。

4、master.info和relay-log.info
从属复制服务器在数据目录中另外创建两个小文件。这些状态文件默认名为主master.info和relay-log.info。 它们包含SHOW SLAVE STATUS语句的输出所显示的信息(关于该语句的描述参见13.6.2节,“用 于控制从服务器的SQL语句”)。状态文件保存在硬盘上,从服务器关闭时不会丢失。下次从服务器启动时,读取这些文件 以确定它已经从主服务器读取了多少二进制日志,以及处理自己的中继日志的程度。

举例如下:
[root@localhost db]# cat master.info
14
localhost-bin.000007
132857066
192.168.0.1
dbuser
pw
3306
60
0

[root@localhost db]# cat relay-log.info
/home/db/localhost-relay-bin.005944
239
localhost-bin.000007
132857066
7066

一个老外的my.cnf的配置,很有价值参考,呵呵

HOWTO: configure MySQL’s my.cnf file
UPDATE: I recently used this MySQL tuner script, I basically went with what it told me, but I’m using a higher query_cache_size than it recommends, basically because I don’t see anything online saying it will hurt things.  So I’m now using the following values on my server:

[mysqld]
user=mysql
bind-address=127.0.0.1
datadir=/var/lib/mysql
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysql/mysql.sock
port=3306
tmpdir=/tmp
language=/usr/share/mysql/english
skip-external-locking
query_cache_limit=64M
query_cache_size=32M
query_cache_type=1
max_connections=15
max_user_connections=300
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_stack=128K
thread_cache_size=128
myisam-recover=BACKUP
key_buffer=64M
join_buffer=1M
max_allowed_packet=32M
table_cache=512M
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
max_connect_errors=10
thread_concurrency=4
myisam_sort_buffer_size=32M
skip-locking
skip-bdb
expire_logs_days=10
max_binlog_size=100M
server-id=1
[mysql.server]
user=mysql
basedir=/usr
[safe_mysqld]
bind-address=127.0.0.1
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192
SAFE_MYSQLD_OPTIONS=”–defaults-file=/etc/my.cnf –log-slow-queries=/var/log/slow-queries.log”
[mysql]
[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[mysqlhotcopy]
interactive-timeout
max_heap_table_size = 64 M
tmp_table_size = 64 M
!includedir /etc/mysql/conf.d/I’ve gone back and forth over the years configuring MySQL for optimal performance, and while I know I’m not there, I now have a new baseline to build from. From a post called Standard MYSQL my.cnf configuration, you can see all the base information, but also things like:

 key_buffer=256M # 64M for 1GB, 128M for 2GB, 256 for 4GBWhich defines the value (256M) but then spells out ideal base values for you to start with if you have more RAM on your system. This is very helpful, I’m tried to go a step further by combining it with Debian’s default my.cnf that comes on 5.0 (lenny) for MySQL 5. As I’m always open for suggestions for improvements, please comment if you have a different view on these choices, thanks. Here it is:

[client]
socket=/var/run/mysqld/mysqld.sock
port=3306

[mysqld_safe]
socket=/var/run/mysqld/mysqld.sock
nice=0

[mysqld]
user=mysql
bind-address=127.0.0.1
datadir=/var/lib/mysql
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysql/mysql.sock
port=3306
tmpdir=/tmp
language=/usr/share/mysql/english
skip-external-locking
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=3000
max_user_connections=600
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_stack=128K
thread_cache_size=128
myisam-recover=BACKUP
#key_buffer – 64M for 1GB, 128M for 2GB, 256 for 4GB
key_buffer=64M
#join_buffer_size – 1M for 1GB, 2M for 2GB, 4M for 4GB
join_buffer=1M
max_allowed_packet=32M
table_cache=1024
#sort_buffer_size – 1M for 1GB, 2M for 2GB, 4M for 4GB
sort_buffer_size=1M
#read_buffer_size – 1M for 1GB, 2M for 2GB, 4M for 4GB
read_buffer_size=1M
#read_rnd_buffer_size – 768K for 1GB, 1536K for 2GB, 3072K for 4GB
read_rnd_buffer_size=768K
max_connect_errors=10
thread_concurrency=4
#myisam_sort_buffer_size – 32M for 1GB, 64M for 2GB, 128 for 4GB
myisam_sort_buffer_size=32M
skip-locking
skip-bdb
expire_logs_days=10
max_binlog_size=100M
server-id=1

[mysql.server]
user=mysql
basedir=/usr

[safe_mysqld]
bind-address=127.0.0.1
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192
SAFE_MYSQLD_OPTIONS=”–defaults-file=/etc/my.cnf –log-slow-queries=/var/log/slow-queries.log”

#[mysqldump]
#quick
#quote-names
#max_allowed_packet=16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
#key_buffer – 64M for 1GB, 128M for 2GB, 256M for 4GB
key_buffer=64M
#sort_buffer – 64M for 1GB, 128M for 2GB, 256M for 4GB
sort_buffer=64M
#read_buffer – 16M for 1GB, 32M for 2GB, 64M for 4GB
read_buffer=16M
#write_buffer – 16M for 1GB, 32M for 2GB, 64M for 4GB
write_buffer=16M

[myisamchk]
#key_buffer – 64M for 1GB, 128M for 2GB, 256M for 4GB
key_buffer=64M
#sort_buffer – 64M for 1GB, 128M for 2GB, 256M for 4GB
sort_buffer=64M
#read_buffer – 16M for 1GB, 32M for 2GB, 64M for 4GB
read_buffer=16M
#write_buffer – 16M for 1GB, 32M for 2GB, 64M for 4GB
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

!includedir /etc/mysql/conf.d/

升级discuzx出现的问题

 

1、ALTER TABLE pre_forum_attachment AUTO_INCREMENT = 66199000;

2、    把source\d7.2_x1.0\table\membermagics.php文件中的33行和44行的INSERT INTO修改成REPLACE INTO.
   然后再重新运行升级试一下。
MySQL Error
Message: query_error
SQL: INSERT INTO `dbname`.pre_home_friendlog SET `uid`=’457116′,`fuid`=’116949′,`action`=’add’,`dateline`=’1278779527′
Error: Duplicate entry ‘457116-116949’ for key 1
Errno.: 1062

sql server 2005的版本号

SQL Server 2005 RTM , 9.0.1399
SQL Server 2005 Service Pack 1 , 9.0.2047
SQL Server 2005 Service Pack 2 , 9.0.3042  
SQL Server 2005 Service Pack 3 , 9.0.4035

如果做数据库镜像,最好再sp3以后,查看自己的版本号sql语句:select @@version