Mysql Tricks : Print defaults for the current client connection

Print defaults for the current client connection $ my_print_defaults client mysql --port=3306 --socket=/tmp/mysql.sock --no-auto-rehash Note, for client connections, you can put setting in the user's .my.cnf file. ... Read more..

March 8th, 2010 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Create a basic MySQL table

Creating tables in databases is the  first step to store your data To create a table you must describe the columns and their atributes. We will create a table to hold contact information with four columns: contact_id, name, email, and birthdate. contact_id column =  integer number that is 20 decimal places long ( it is created with an INT(20) datatype). name column holds the full name of a contact, which we  will will set be no longer than 50 characters long, so the ... Read more..

February 7th, 2010 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Mysql Tricks : Remove duplicate entries

Remove duplicate entries. Assume the following table and data. CREATE TABLE IF NOT EXISTS dupTest ( pkey int(11) NOT NULL auto_increment, a int, ... Read more..

December 12th, 2009 admin Posted in MySQL 1 Comment »

AddThis Social Bookmark Button

Change max_allowed_packet setting fast

If you want to change the  max_allowed_packet fast, without modifying any confis or restart mysql ? Follow this howto : mysql> show variables like 'max%' ; +-----------------------+------------+ | Variable_name | Value | +-----------------------+------------+ | max_allowed_packet | 1048576 | ... mysql> set max_allowed_packet = 1500000; Query OK, 0 rows affected (0.03 sec) mysql> show variables like 'max%' ; +-----------------------+------------+ | Variable_name | Value ... Read more..

December 5th, 2009 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Mysql Tricks : Can the order of the columns in a create statement make a difference?

Can the order of the columns in a create statement make a difference? YES create table t ( a int, b int, timeUpdate timestamp, timeEnter timestamp ); The first timestamp will always be the "automatically ... Read more..

November 26th, 2009 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

MySQL dump import -> Error 1217

If you use  innodb tables and foreign key constraints, importing a mysqldump may generate foreign key errors. To resolve this issue please read on : At the beginning of the dump file add this : SET FOREIGN_KEY_CHECKS=0; At the end of the dump file add this : SET FOREIGN_KEY_CHECKS=1; This will disable foreign key checks for the mysqldump import session only. This will allow the data to be imported without the error being generated. Read more..

October 31st, 2009 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Rename or change name of MySQL table

This is how to change the name of an existing table tableone to tabletwo : RENAME TABLE  tableone TO tabletwo;  Note : you must issue this command with an user that has the correct priviledges Read more..

October 25th, 2009 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Import CSV file directly into MySQL

You can use mysql to import directly a csv file . You do not need to make a script to do this anymore From an Excel file , you need to export it as CSV ,  Remove the CSV Headers and the Excel data  from the file ,  and after that it can be inserted in MySql Here is an example : load data local infile 'your.csv' into table tblUniq fields terminated by ',' enclosed by '"' lines terminated by '\n' (yourName, yourCity, yourComments) Read more..

October 25th, 2009 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Mysql Tricks : Show status information on a table

Show status information on a table. Note, if the database was started with --safe-show-database or --skip-show-database some of these commands may not work. Note the "\G" option may provide a nicer format. Show the create statement: mysql> show create table dupTest\G ... Read more..

October 13th, 2009 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Create a MySQL database

Log in MySql To create a database called 'yourdatabase' use the following SQL command: create database yourdatabase; Read more..

October 5th, 2009 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Mysql Tricks : Clean up binary log files

Clean up binary log files. For a default install they may be in /usr/local/var/ or /var/lib/mysql/ with names ending in -bin.000001,-bin.000002,.. The following command may help find out where the logs are located. mysql> show variables like '%home%'; ... Read more..

September 25th, 2009 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Drop or delete a table in MySQL

This is how to remove a table from a MySQL database, and remove all of its data, use the following SQL command: 1.  log in mysql 2.  issue command : use yourdatabase;  3. issue command : drop table if exists yourtable; The last command will delete the database if it exists , it is usefull because if it does not exist it will not output any error Read more..

September 13th, 2009 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Increment an exisitng value in Mysql

To increment or decrement and existing number in a table (you do not need to read the value first)  please read on : Increment the value 'timer' by one for the row in table 'movies' where 'movie_id' is '24', use: UPDATE movies SET timer=timer+1 WHERE movie_id=24 Note  to decrement use timer=timer-1 Enjoy Read more..

August 22nd, 2009 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Mysql Tricks : Find out who is doing what, and kill the process if needed.

Find out who is doing what, and kill the process if needed. This example kills Id 657. mysql> show processlist; show processlist; +-----+------+-----------+---------+---------+-------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | ... Read more..

August 10th, 2009 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Recovering / Changing Your MySQL Root Password

Recovering / Changing Your MySQL Root Password Sometimes you may have to recover the MySQL root password because it was either forgotten or misplaced. The steps you need are: 1) Stop MySQL [root@bigboy tmp]# service mysqld stop Stopping MySQL:  [  OK  ] [root@bigboy tmp]# 2) Start MySQL in Safe mode with the mysqld_safe command and tell it not to read the grant tables with all the MySQL database passwords. [root@bigboy tmp]# mysqld_safe --skip-grant-tables --skip-networking & [1] 13007 [root@bigboy tmp]# Starting mysqld daemon with databases from /var/lib/mysql [root@bigboy tmp]# Note: In Fedora ... Read more..

July 23rd, 2009 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Mysql Tricks : Transactions

Transactions: Not all table types support transactions. BDB and INNODB type do support transactions. Assuming the server has NOT been started with --skip-bdb or --skip-innodb the following should work: mysql> create table tran_test (a int, b int) type = InnoDB; mysql> begin; mysql> insert into tran_test (a,b) values (1,2); ... Read more..

July 17th, 2009 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Create a MySQL user account

This is how to create a user youruser with password YourPassword  and grant them full access to the  database named yourdatabase, connect to the database with mysql and issue the command: grant all on yourdatabase.* to user@localhost identified by 'YourPassword'; Read more..

July 4th, 2009 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Mysql Tricks : Monitor port 3306

Monitor port 3306: tcpdump -i eth0 -nN -vvv -xX -s 1500 port 3306 and host not 192.168.1.102 The -s is length of each packet. This monitors all traffic on port 3306 excluding the good client 192.168.1.102. The out will be in the following format: [root@third-fl-71 root]# tcpdump -i eth0 -nN -vvv -xX -s 1500 port ... Read more..

May 7th, 2009 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Display a list of databases on a MySQL server

This is how to list the databases that exist in a MySQL server, use the 'show databases' SQL command: show databases; +------------------+ | Database                | +------------------+ | database1              | | mysql                     | | test                         | +------------------+ It will display all the databases your user has access to Read more..

April 25th, 2009 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

MySQL Replace Function

MySQL Replace(), here's the syntax. update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]'); Read more..

January 28th, 2009 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button


Page 1 of 212