SQL 批量插入1000条测试数据

declare @i int;--申明一个整形变量i

set @i = 1;--设置初始值为1

while(@i < 1000)--用while循环给定一个循环结束条件小于1000

begin

insert into T_Kh_SocialAct(客户编号,互动备注) values(1003,'user_no'+cast(@i as varchar));--开始插入

set @i = @i +1;--累加

end;

Posted in 数据库 at April 12, 2019. by 傻猫 .    Views: 148    No Comments

mysql DBA技能

主备

启动slave thread:登录到sql后运行start slave;
查看主备:show slave status\G

主备切换

发生切换的原因是ha在固定时间内来测试数据库能否访问,如果数据库在多长时间内一直不能访问才会发生主备切换
所以主备切换的原因是一定时间内数据库无法访问,原因可能有qps过大或慢sql rt过高导致数据库堵住,或线程池满数据库无法访问,连接满拒绝访问

数据

1.max_connections:允许所有用户的最大连接数,包括root除了(‘root’@‘127.0.0.1’和’root’@’localhost’),满了报错too many connections,错误号1040
2.max_user_connections:单个用户允许的最大连接数,包括root除了(‘root’@‘127.0.0.1’和’root’@’localhost’),满了报错User root already has more than 'max_user_connections' active connections,错误号1203
3.数据库线程池:32 thread_pool_size=32,thread_pool_oversubscribe=10;线程池大小为32*(10+1)=352,加上其它后台线程也不过400

查看更多内容...

Posted in 数据库 at September 9, 2018. by 傻猫 .    Views: 1385    No Comments

统计各表记录数和空间占用情况

SELECT s.Name AS SchemaName,
t.NAME AS TableName,
SUM(p.rows)/COUNT(distinct a.type) AS RowCounts,
CAST(SUM(a.total_pages) as float) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
SUM(a.data_pages) * 8 AS DataSpaceKB,
CAST((SUM(a.total_pages) - SUM(a.used_pages)) as float) * 8 AS UnusedSpaceKB,
MIN(FILEGROUP_NAME(a.data_space_id)) as FileGroup
FROM sys.tables t
JOIN sys.schemas s
ON s.schema_id = t.schema_id
JOIN sys.indexes i
ON t.OBJECT_ID = i.object_id
JOIN sys.partitions p
ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 AND i.index_id < 2
GROUP BY t.Name, s.Name
ORDER BY TotalSpaceKB desc

Posted in 数据库 at January 7, 2018. by 傻猫 .    Views: 3949    No Comments

CentOS环境开放3306端口

添加规则,打开3306端口
iptables -I INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

查看规则是否生效
iptables -L -n //或者 service iptables status

在mysql控制台下修改权限
grant all privileges on . to 'root'@'%' identified by '123456' with grant option;

flush privileges; //重载系统权限

exit;//退出mysql控制台

Posted in 数据库 at September 13, 2017. by 傻猫 .    Views: 4631    No Comments

CentOS 7.0下使用yum安装MySQL

CentOS7默认数据库是mariadb,配置等用着不习惯,因此决定改成mysql,但是CentOS7的yum源中默认好像是没有mysql的。为了解决这个问题,我们要先下载mysql的repo源。

1.下载mysql的repo源

$ wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

2.安装mysql-community-release-el7-5.noarch.rpm包

$ sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm

安装这个包后,会获得两个mysql的yum repo源:/etc/yum.repos.d/mysql-community.repo,/etc/yum.repos.d/mysql-community-source.repo。

3.安装mysql

$ sudo yum install mysql-server

根据提示安装就可以了,不过安装完成后没有密码,需要重置密码

4.重置mysql密码

$ mysql -u root

登录时有可能报这样的错:ERROR 2002 (HY000): Can‘t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock‘ (2),原因是/var/lib/mysql的访问权限问题。下面的命令把/var/lib/mysql的拥有者改为当前用户:

$ sudo chown -R root:root /var/lib/mysql

重启mysql服务

$ service mysqld restart

接下来登录重置密码:

$ mysql -u root //直接回车进入mysql控制台
mysql > use mysql;
mysql > update user set password=password('123456') where user='root';
mysql > exit;

Posted in 数据库 at September 12, 2017. by 傻猫 .    Views: 4236    No Comments

SQL显示某表的字段名和说明备注

Select A.name As 字段名,C.value As 说明
From syscolumns A
Inner Join sysobjects B on A.id=B.id
Left Join sys.extended_properties C on A.id=c.major_id and a.colid=C.minor_id Where B.name='User_Order'

Posted in 数据库 at March 20, 2017. by 傻猫 .    Views: 2759    No Comments

数据库各表统计信息,记录数/占用空间

create table #Data(name varchar(100),row varchar(100),reserved varchar(100),data varchar(100),index_size varchar(100),unused varchar(100))

declare @name varchar(100)
declare cur cursor for
select name from sysobjects where xtype='u' order by name
open cur
fetch next from cur into @name
while @@fetch_status=0
begin
insert into #data
exec sp_spaceused @name
print @name

fetch next from cur into @name 

end
close cur
deallocate cur

create table #DataNew(name varchar(100),row int,reserved int,data int,index_size int,unused int)

insert into #dataNew
select name,convert(int,row) as row,convert(int,replace(reserved,'KB','')) as reserved,convert(int,replace(data,'KB','')) as data,
convert(int,replace(index_size,'KB','')) as index_size,convert(int,replace(unused,'KB','')) as unused from #data

select * from #dataNew order by data desc

Posted in 数据库 at January 22, 2017. by 傻猫 .    Views: 3138    No Comments

mssql里求数据库中所有表的记录数

create table #temp(name varchar(50),count int)
declare @tablename varchar(50),@sql varchar(500)
declare gettablenum_cur cursor for select name from sysobjects where xtype='u'
open gettablenum_cur
fetch gettablenum_cur into @tablename
while @@fetch_status=0
begin
select @sql='insert #temp(name,count) '+'select '+''''+@tablename+''''+','+'count(1) from '+@tablename
exec(@sql)
fetch gettablenum_cur into @tablename
end
select * from #temp order by name
close gettablenum_cur
deallocate gettablenum_cur
drop table #temp

Posted in 数据库 at January 22, 2017. by 傻猫 .    Views: 2872    No Comments