MySQL delete all rows of tables back to zero id

To delete all rows in the table following these codes:

TRUNCATE first_table;
TRUNCATE second_table;
TRUNCATE third_table;
TRUNCATE fourth_table;

MySQL import file from MySQL console

Here's the step:
Login to your MySQL database:

mysql -u root -p

Select the database name:

mysql>use db_name;

Import the database:

mysql>source /home/dump.sql;

ERROR 1153 (08S01) at line 75: Got a packet bigger than 'max_allowed_packet' bytes

These error message raise when i run the script like the below:

mysql -u root -p my_database < dump.sql

Then raised:

ERROR 1153 (08S01) at line 75: Got a packet bigger than 'max_allowed_packet' bytes

Googling and found:

vi /etc/my.cnf

add these line under [mysqld]

[mysqld]
# added to avoid err "Got a packet bigger than 'max_allowed_packet' bytes"
#
net_buffer_length=1000000 
max_allowed_packet=1000000000
#

Restart the mysql db:

service mysqld restart

Finally, i import the database with successfully:

mysql -u root -p my_database < dump.sql

How to restore MySQL Database (MySQL won't start because InnoDB Corruption and Recovery)

Here's the information when i show the error log of MySQL:

130306 22:02:18 mysqld_safe Number of processes running now: 0
130306 22:02:18 mysqld_safe mysqld restarted
130306 22:02:18 [Note] Plugin 'FEDERATED' is disabled.
130306 22:02:18 InnoDB: The InnoDB memory heap is disabled
130306 22:02:18 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130306 22:02:18 InnoDB: Compressed tables use zlib 1.2.3
130306 22:02:18 InnoDB: Using Linux native AIO
130306 22:02:18 InnoDB: Initializing buffer pool, size = 128.0M
130306 22:02:18 InnoDB: Completed initialization of buffer pool
130306 22:02:18 InnoDB: highest supported file format is Barracuda.

Googling and fix the problem:

sudo service mysqld stop

Backup your Database:

mkdir -p /opt/database/backup/

cp -r /var/lib/mysql/* /opt/database/backup/

Adding script for recovery:

vi /etc/my.cnf

then add this line

innodb_force_recovery = 4

Restart your mysql:

service mysqld restart

Dump all tables:

mysqldump -u root -p db_name > dump.sql

Drop all databases which need recovery:

mysql>DROP TABLE table_name;

Stop MySQL:

service mysqld stop

rm -rf /var/lib/mysql/ib*

Comment out recovery script:

vi /etc/my.cnf

#innodb_force_recovery

Restart your mysql:

service mysqld restart

Restore your tables of database:

mysql -u root -p db_name < dump.sql

Installing mysql gem on Centos 5

Firstly, install mysql dev:

sudo yum install mysql-devel

gem install mysql2

How To Install Linux, Apache, MySQL, PHP on Centos 5

Firsly update packages on Centos

sudo yum update

1. Install Apache

sudo yum install httpd

sudo service httpd start

2. Install MySQL

sudo yum install mysql-server

sudo service mysqld start

You can set a root MySQL password with these command:

sudo /usr/bin/mysql_secure_installation

3. Install PHP

sudo yum install php php-mysql

   PHP Modules

You can see the libraries that are available by typing:

yum search php-

Then terminal will show list of modules:

php53-process.i386 : Modules for PHP script using system process interfaces
php53-pspell.i386 : A module for PHP applications for using pspell interfaces
php53-snmp.i386 : A module for PHP applications that query SNMP-managed devices
php53-soap.i386 : A module for PHP applications that use the SOAP protocol
php53-xml.i386 : A module for PHP applications which use XML
php53-xmlrpc.i386 : A module for PHP applications which use the XML-RPC protocol

To see more detail:

yum info name_of_module

To install the module:

yum install name_of_module

Finally, restart your apache to see the effect for the changed:

sudo service httpd restart

Library not loaded: libmysqlclient.18.dylib (LoadError) on Mac OS

When installed raisl 4.0.1, it's raise some error:

/opt/local/lib/ruby2.0/gems/2.0.0/gems/mysql2-0.3.14/lib/mysql2.rb:8:in `require': dlopen(/opt/local/lib/ruby2.0/gems/2.0.0/extensions/x86_64-darwin-13/2.0.0/mysql2-0.3.14/mysql2/mysql2.bundle, 9): Library not loaded: libmysqlclient.18.dylib (LoadError)
  Referenced from: /opt/local/lib/ruby2.0/gems/2.0.0/extensions/x86_64-darwin-13/2.0.0/mysql2-0.3.14/mysql2/mysql2.bundle
  Reason: no suitable image found.  Did find

to fix this problem, type command below:

sudo ln -s /usr/local/mysql/lib/libmysqlclient.18.dylib /usr/lib/libmysqlclient.18.dylib

Installing MySQL on Mac OS X

Before you are download, ensure your architecture of Mac:
Oper your terminal and type

uname -a

and the result are:

Darwin aruls-mbp 13.0.0 Darwin Kernel Version 13.0.0: Thu Sep 19 22:22:27 PDT 2013; root:xnu-2422.1.72~6/RELEASE_X86_64 x86_64

The "RELEASE_X86_64 x86_64", it's mean your computer is using 64 Bit architecture computer.

Firstly, download the MySQL 64 Bit software

Double click at file "mysql-5.6.15-osx10.7-x86_64.dmg" and will appear popup like this:

Ok, next step is:

Install the mysql-5.6.15-osx10.7-x86_64.dmg
Install the MySQLStartupItem.pkg
Install the MySQL.prefpane

Then click "Start MySQL Server" and "Check Automatically Start on startup"
We need do something to using "mysql" command work on the Terminal, follow the step
Open up your terminal:

vi ~/.profile

Use the "i" key, to insert mode, then paste this line:
"export PATH=$PATH:/usr/local/mysql/bin"

Hit the ‘Esc’ key to exit insert mode and type “:wq” and hit enter to write and quit the editor. Close the terminal window and open a new one. You can then enter this command:

mysql -u root -p

Set MySQL password

/usr/local/mysql/bin/mysqladmin -u root password SECRET

Setting the Socket The last command we'll need to run in terminal will allow Apache and PHP to access MySQL:

sudo mkdir /var/mysql

sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock

Installing MySQL on Linux Ubuntu Server 12.04 LTS

Firsly, we are must check the current version
Just simple way to check the current installed the Database.

mysql

Next, we'll install the package MySQL

sudo apt-get install mysql-server mysql-client

That's all, now you can check the MySQL was installed on your mechine

mysql --version

Next time you able to restart your db, here we go

sudo /etc/init.d/mysql restart

Using pattern REGEXP for detecting number in the end of the string

The sample code:

SELECT * FROM table WHERE column REGEXP '[0-9]$'

Backup database with Crontab

How to backup database with schedule. here the codes. First let's create a chunk of codes to backup

nano /home/arul/backup_database.sh

and type these code:

mysqldump -u dbuser -pdbpass dbname > /home/arul/backup/file_name_backup_`date +\%Y%m%d`.sql

then let's set the schedule to execute the code with Crontab
firstly, edit your crontab.

crontab -e

type the code:

0 0 * * * /home/arul/backup_database.sh

You can list crontab execution code with:

crontab -l

Manage Hierarchical Data in MySQL

This very helpul for organiziation structure..

check it out:
http://mirror.neu.edu.cn/mysql/tech-resources/articles/hierarchical-data.html

Installing Apache2 with PHP5 and MYSQL support

Open your terminal and enter the following command to start installation

1. Installing Apache2

sudo apt-get install apache2

2. Installing PHP5

sudo apt-get install php5 libapache2-mod-php5

3. Installing MYSQL

sudo apt-get install mysql-server mysql-client

sudo apt-get install php5-mysql

sudo apt-get install phpmyadmin

Export or Import database to sql file with command line

How to quickly export/import sql file?, you can do that and use some tools here but i want to share about new experience, on this case i used the mysql commands. follow these steps:


1. Export database to sql file (create mysql dump file)
The easiest way to export use syntax command like this:

> mysqldump -u username -p database_name  > export_filename.sql

2. Import mysql dump file to database.
Make sure you have make the database name firstly, next you can use these command:

> mysql -u username -p database_name < export_filename.sql

hope helpful :)