mysql-ibdata1瘦身

mysql-ibdata1瘦身

mysql ibdata1瘦身


1# 备份数据库(如有存储过程或自定义函数需添加参数  –routines):
root 用户操作
/usr/bin/mysqldump -uroot -proot –skip-opt –quick –force –add-drop-database –routines –all-databases –add-drop-table > /data/tmp/mysqldump.sql

2# 停止数据库
sudo /etc/init.d/mysql stop

3# 删除文件
root用户操作
rm /data/mysql/ibdata1
rm /data/mysql/ib_logfile*

4#然后启动数据库
sudo /etc/init.d/mysql start

或者

sudo services mysql start

5# 还原数据
/usr/bin/mysql -uroot -proot < /data/tmp/mysqldump.sql

Linux下C++访问MySQL

Linux下C++访问MySQL

转载:http://curran.blog.51cto.com/2788306/533169

今天给大家演示在linux下如果用C++操作mysql

1:安装MySQL
挂载光盘:

mkdir /cdrom

mount /dev/hdc /cdrom

cd /cdrom/Server

rpm -ivh perl-DBI-1.52-2.el5.i386.rpm

rpm -ivh mysql-5.0.45-7.el5.i386.rpm mysql-bench-5.0.45-7.el5.i386.rpm mysql-devel-5.0.45-7.el5.i386.rpm

rpm -ivh perl-DBD-MySQL-3.0007-2.el5.i386.rpm

rpm -ivh mysql-server-5.0.45-7.el5.i386.rpm mysql-test-5.0.45-7.el5.i386.rpm

查看mysql服务状态:

service mysqld status

启动服务:

service mysqld start

连接数据库:

mysql

2:安装gcc工具包
rpm -ivh gcc* –force –nodeps 强制安装

3:进入mysql
create table t1 (id 
int,name varchar(30));

t1表插入数据:

1.cpp

#include <iostream>

#include <mysql/mysql.h>

#include <string>

using namespace std;

main() 

{

     MYSQL mysql;

     mysql_init(&mysql);

     mysql_real_connect(&mysql, 
"localhost"
"root"
"root"
"test", 3306, NULL, 0);

     string sql = 
"insert into t1 (id, name) values (1, ‘java1’);";

     mysql_query(&mysql, sql.c_str());

     mysql_close(&mysql);

}

g++ -o 1.out 1.cpp -lmysqlclient -I/usr/include/mysql/ -L/usr/lib/mysql

 更新mysql数据;

1.cpp

#include <iostream>

#include <mysql/mysql.h>

#include <string>

using namespace std;

main()

{

     MYSQL mysql;

     mysql_init(&mysql);

     mysql_real_connect(&mysql, 
"localhost"
"root"
"root"
"test", 3306, NULL, 0);

     string sql = 
"insert into t1 (id,name) values (2, ‘java2’),(3, ‘java3’);";

     mysql_query(&mysql, sql.c_str());

     sql = 
"update t1 set name = ‘java33’ where id = 3;";

     mysql_query(&mysql, sql.c_str());

     mysql_close(&mysql);

}

g++ -o 1.out 1.cpp -lmysqlclient -I/usr/include/mysql/ -L/usr/lib/mysql

./1.out

 mysql 存储过程:

mysql>delimiter 
//

        >create procedure p01()

        >begin

        >insert into t1 (id,name) values (66, ‘java66’);

        >end;

        >
//

应用存储过程:

mysql>delimiter 
//

        >create procedure p01()

        >begin

        >insert into t1 (id,name) values (66, ‘java66’);

        >end;

        >
//

触发器:

新建t2表:

mysql>delimiter 
//

     >create trigger tr1 after insert on t1 
for each row

     >begin

     >insert into t2 (id,name) values (
new.id,
new.name);

     >end;

     >
//

     >delete from t1 where id = 66;

     >
//

     >delimeter ;

vi 3.cpp

#include <iostream>

#include <string>

#include <mysql/mysql.h>

using namespace std;

main()

{

    MYSQL mysql;

    mysql_init(&mysql);

    mysql_real_connect(&mysql, 
"localhost"
"root"
"root", 3306, NULL, 0);

    string sql = 
"call p01();";

    mysql_query(&mysql, sql.c_str());

    mysql_close(&mysql);

}

g++ -o 3.out 3.cpp -lmysqlclient -I/usr/include/mysql/ -L/usr/lib/mysql

./3.out

mysql>delimeter ;

     >select * from t1;

     >select * from t2;

     >存在数据(66, ‘java66’)

查看表中数据的总数:
vi 4.cpp

#include <iostream>

#include <string>

#include <mysql/mysql.h>

using namespace std;

main()

{

    MYSQL mysql;

    MYSQL_RES *result = NULL;

    mysql_init(&mysql);

    mysql_real_connect(&mysql, 
"localhost"
"root"
"root"
"test", 3306, NULL, 0);

    string sql = 
"select id,name from t1;";

    mysql_query(&mysql, sql.c_str());

    result = mysql_store_result(&mysql);

    
int rowcount = mysql_num_rows(result);

    cout << rowcount << endl;

    mysql_close(&mysql);

}

g++ -o 4.out 4.cpp -lmysqlclient -I/usr/include/mysql/ -L/usr/lib/mysql

./4.out

查处字段的总数:
vi 5.cpp

#include <iostream>

#include <string>

#include <mysql/mysql.h>

using namespace std;

main()

{

    MYSQL mysql;

    MYSQL_RES *result = NULL;

    mysql_init(&mysql);

    mysql_real_connect(&mysql, 
"localhost"
"root"
"root", 3306, NULL, 0);

    string str = 
"select id,name from t1;";

    mysql_query(&mysql, sql.c_str());

    result = mysql_store_result(&mysql);

    
int rowcount = mysql_num_rows(result);

    cout << rowcount << endl;

    
int fieldcount = mysql_num_fields(result);

    cout << fieldcount << endl;

    mysql_close(&mysql);

}

g++ -o 5.out 5.cpp -lmysqlclient -I/usr/include/mysql/ -L/usr/lib/mysql

./5.out

列出具体字段名:
vi 6.cpp

#include <iostream>

#include <string>

#include <mysql/mysql.h>

using namespace std;

main()

{

    MYSQL mysql;

    MYSQL_RES *result = NULL;

    MYSQL_FIELD *field = NULL;

    mysql_init(&mysql);

    mysql_real_connect(&mysql, 
"localhost"
"root"
"root"
"test", 3306, NULL, 0);

    string str = 
"select id,name from t1;";

    mysql_query(&mysql, sql.c_str());

    result = mysql_store_result(&mysql);

    
int rowcount = mysql_num_rows(result);

    cout << rowcount << endl;

    
int fieldcount = mysql_num_fields(result);

    cout << fieldcount << endl;

    
for(
int i = 0; i < fieldcount; i++)

    {

     field = mysql_fetch_field_direct(result,i);

     cout << field->name << 
"/t/t";

    }

    cout << endl;

    mysql_close(&mysql);

}

g++ -o 6.out 6.cpp -lmysqlclient -I/usr/include/mysql/ -L/usr/lib/mysql

./6.out

显示表中所有数据:
vi 7.cpp

#include <iostream>

#include <string>

#include <mysql/mysql.h>

using namespace std;

main()

{

    MYSQL mysql;

    MYSQL_RES *result = NULL;

    MYSQL_FIELD *field = NULL;

    mysql_init(&mysql);

    mysql_real_connect(&mysql, 
"localhost"
"root"
"root", 3306, NULL, 0);

    string str = 
"select id,name from t1;";

    mysql_query(&mysql, sql.c_str());

    result = mysql_store_result(&mysql);

    
int rowcount = mysql_num_rows(result);

    cout << rowcount << endl;

    
int fieldcount = mysql_num_fields(result);

    cout << fieldcount << endl;

    
for(
int i = 0; i < fieldcount; i++)

    {

     field = mysql_fetch_field_direct(result,i);

     cout << field->name << 
"/t/t";

    }

    cout << endl;

    MYSQL_ROW row = NULL;

    row = mysql_fetch_row(result);

    
while(NULL != row)

    {

     
for(
int i=1; i<fieldcount; i++)

     {

        cout << row[i] << 
"/t/t";

     }

     cout << endl;

     row = mysql_fetch_row(result);

    }

    mysql_close(&mysql);

}

g++ -o 7.out 7.cpp -lmysqlclient -I/usr/include/mysql/ -L/usr/lib/mysql

./out