Prabhat's Tech Journal

Yesterday we have requirement to send report to our senior in excel format by retrieving data from database . Since there are only 10 rows so we can also manually create excel. But like other programmers, I am also very lazy, so that we create this script to export values from Mysql in csv format. mysql -u mysqlusername -p mysqlpass databsename -B -e "select * from \` tabalename \`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > mysql_exported_table.csv After that we fount that there are many other programming methods to perform this task.

mysql -u user -p -e 'SQL Query' database Where, -u : Specify mysql database user name -p : Prompt for password -e : Execute sql query database : Specify database name To list all database, enter: $ mysql -u replicant -p -e 'show databases;' To list count all rows, enter: $ mysql -u replicant -p -e 'SELECT COUNT(*) FROM remote_logs' Sample output: Enter password: +----------+ | count(*) | +----------+ | 471 | +----------+ Tell mysql to display output a page at a time, by using more or less pager: $ mysql --pager=/usr/bin/less -u replicant -p -e 'SELECT COUNT(*) FROM remote_logs' Redirect out to a file: $ mysql -u replicant -p 'PassWord' -e 'SELECT COUNT(*) FROM quotes' remote_logs > sql.output.txta To avoid password prompt just create ~/my.cnf file: [client] # for local server #host=localhost host=10.128.150.205 user=replicant password=myPassword [mysql] pager=/usr/bin/less

You can change the prompt on the MySQL command-line tool to show user name, time, current database or many other useful facts. mysql> prompt \R:\m:\s> PROMPT set to '\R:\m:\s>' 11:46:16>show tables; +----------------------+ | Tables_in_wordpress | +----------------------+ | wp_categories | | wp_comments | | wp_linkcategories | +---------------------+ 9 rows in set (0.00 sec) 11:46:24> If you specify the prompt command with no argument, mysql resets the prompt to the default of mysql> . The prompt command reconfigures the default mysql> prompt. The string for defining the prompt can contain the following special sequences: Option Description \c A counter that increments for each statement you issue \D The full current date \d The default database \h The server host \l The current delimiter (new in 5.0.25) \m Minutes of the current...

Problem: I want to change url path or a string from mysql table. Solution: Do it manually? No way. There are hundreds of them! There are basically two ways - First, output the database table and use text editor's "search and replace" function. Second, use MySQL REPLACE function . Here is the SQL query to replace string in database table :- UPDATE table_name SET column_name = REPLACE ( column_name, "original_string" , "replace_string" );

I have a database table with a auto increment column for primary key. As the records being add and delete many times, the auto increment value will keep increasing. Problem One: I want to delete all data from table. Solution: TRUNCATE table tbl_name this command will delete everything from the table and start again from 1.... DELETE just does the same but keeps the current auto_increment value.... Problem Two: If I have entered 10 records, and deleted 9th, 10th records. The next auto increment value will be 11, not 9. Solution: Run a query: ALTER TABLE tablename AUTO_INCREMENT = 1 This will reset the next auto increment value to current largest value in the auto increment column + 1. So, the auto increment value of next inserted record will start from 9. Problem Three: If I have entered 10 records, and deleted center records - 4th, 5th. I want to insert next record as 4th not 11th. Solution: Run the following query: SET insert_id = 4; INSERT INTO tablename VALUES (’blah’, ‘…’);...

1. Start. 2. Stop the mysqld daemon process: root@prabhat:~# /etc/init.d/mysql stop 3. Start the mysqld daemon process with the --skip-grant-tables option. : root@prabhat:~# /usr/bin/mysqld_safe --skip-grant-tables & [1] 6702 Starting mysqld daemon with databases from /var/lib/mysql mysqld_safe[6763]: started . 4. Start the mysql client with the -u root option: Note: - In Mysql server running with the --skip-grant-tables flag not require password root@prabhat:~$ mysql --user=root mysql Enter password: 5. Execute the UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root'; mysql> update user set Password=PASSWORD(' new-password-here ') WHERE User='root'; Query OK, 2 rows affected (0.04 sec) Rows matched: 2 Changed: 2 Warnings: 0 6. Execute the FLUSH PRIVILEGES; comman...