Linux Ask! is a Q & A web site specific for Linux related questions. Questions are collected, answered and audited by experienced Linux users.
Drop all tables in MySQL database
Answer:
MySQL does not have a command for removing all database table(s) without dropping the database, to do so, you need the following tricks:
# mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE]
| grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]
Enable external locking in MySQL
Answer:
External locking is a method that allow MySQL aware of external program(s) might also have access to the database tables as the same time, so if other programs locked the files, MySQL will wait until those files are unlocked.
This is not recommended and poor for MySQL performance, so you should turn off it unless you have multiple MySQL servers using the same database directory.
To enable it, edit the MySQL configurations, e.g. /etc/my.cnf
..
external-locking
..Don't forget to restart MySQL to take effect.
# /sbin/service mysqld restart
Changing the default storage engine in MySQL
Answer:
The default storage of MySQL is MyISAM, but you can change to others such as InnoDB.
To change this default, edit the MySQL configurations, e.g. /etc/my.cnf
..
[mysqld]
default-storage_engine = innodb
Don't forget to restart MySQL to take effect.
# /sbin/service mysqld restart
Connect MySQL server using C
Answer:
Below show a code sample for how to connect to MySQL server using C programming language.
#include <stdio.h>
#include <stdlib.h>
#include "mysql.h"
MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;
void exiterr(int exitcode)
{
fprintf( stderr, "%s\n", mysql_error(&mysql) );
exit( exitcode );
}
int main()
{
uint i = 0;
if (!(mysql_connect(&mysql,"host","username","password")))
exiterr(1);
if (mysql_select_db(&mysql,"payroll"))
exiterr(2);
if (mysql_query(&mysql,"SELECT name,rate FROM emp_master"))
exiterr(3);
if (!(res = mysql_store_result(&mysql)))
exiterr(4);
while((row = mysql_fetch_row(res))) {
for (i=0 ; i < mysql_num_fields(res); i++)
printf("%s\n",row[i]);
}
mysql_free_result(res);
mysql_close(&mysql);
}
Clear screen in MySQL client
Answer:
To clear the screen of MySQL client, like the clear command in Linux, use the following tricks:
mysql> pager clear;
PAGER set to 'clear'
mysql> select 1; -- Magic Happened here!
mysql> nopager;
PAGER set to stdout
mysql>