Export MySQL tables into CSV/PSV/TSV format

CSV:

mysql my_db -e "SELECT * FROM my_table" | sed 's/\t/","/g;s/^/"/;s/$/"/;' > my_db.my_table.csv

PSV:

mysql my_db -e "SELECT * FROM my_table" | sed 's/\t/|/g' > my_db.my_table.psv

TSV:

mysql my_db -e "SELECT * FROM my_table" > my_db.my_table.tsv 

kill -9 , A bad idea!

[Source]:http://web.archive.org/web/20080801151452/http://speculation.org/garrick/kill-9.html

Most programs require some sort of cleanup when it exits. These programs setup a signal handler to perform these cleanup duties as a result of SIGTERM and SIGINT. They would setup a signal handler for SIGKILL if they could, but SIGKILL is untrappable.
If a program decided to trap the TERM or INT signals, then it would most likely delete any temporary files, shutdown sockets, remove shared memory segments, close open files, or some other task. For instance, MySQL needs to flush data to disk and close its database files. Killing MySQL with -9 will corrupt your databases. Most user apps that work on some binary formatted data files require temporary files, using kill -9 on these apps would leave around these temporary files. Network daemons need to flush their logs and properly shutdown Internet sockets so that clients aren't left hanging. Killing your web browser can corrupt your bookmarks file, cache files, leave temp files in /tmp (remember that 20MB mpeg you clicked on? yah, it's still sitting in /tmp)
Using kill -9 on a process is like just hitting the power button on your running PC. You already know that powering off a running PC can damage your filesystem because of run-time inconsistent data, why would you do the same thing to your applications? You risk the _exact same_ data corruption.

Therefore, so that your important applications may close themselves, it is very important you follow this procedure when killing them:

kill pid (sends a TERM, wait 5 seconds)
kill pid (yes, try again, wait 5 seconds)
kill -INT pid  (wait for it)
kill -INT pid  (damn, still not dead?)
kill -KILL pid (same thing as -9)
kill -KILL pid (something is wrong)

If the process is still running, then stop sending it signals. It's either stuck in I/O wait or it's Defunct. 'ps auxw | grep processname', if you see it's in state D, then kill its parent process (the 3rd column of 'ps -ef' is the parent pid). If it's in I/O wait, then you have a deeper system problem. Most home users will never have this problem.

Issue:- ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Issue:- ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Solution:- Follow the below steps.

[root@server ~]# /etc/init.d/mysql stop

Stopping MySQL: [ OK ]

[root@servert1 ~]# mysqld_safe --skip-grant-tables &

[root@servert1 ~]# Starting mysqld daemon with databases from /var/lib/mysql



[root@servert1 ~]# mysql -u root

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


mysql> show databases;

+--------------------+

| Database |

+--------------------+

| mysql | 

+--------------------+

1 rows in set (0.13 sec)



mysql> use mysql; 

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A



Database changed

mysql> show tables;

+---------------------------+

| Tables_in_mysql |

+---------------------------+

| columns_priv | 

| db | 

| func | 

| help_category | 

| help_keyword | 

| help_relation | 

| help_topic | 

| host | 

| proc | 

| procs_priv | 

| tables_priv | 

| time_zone | 

| time_zone_leap_second | 

| time_zone_name | 

| time_zone_transition | 

| time_zone_transition_type | 

| user | 

+---------------------------+

17 rows in set (0.00 sec)



mysql> update user set password=PASSWORD("testpass") where User='root';

Query OK, 3 rows affected (0.05 sec)

Rows matched: 3 Changed: 3 Warnings: 0



mysql> flush privileges; 

Query OK, 0 rows affected (0.04 sec)



mysql> quit



[root@server ~]# /etc/init.d/mysql restart

STOPPING server from pid file /var/run/mysqld/mysqld.pid

101120 04:17:15 mysqld ended

Stopping MySQL: [ OK ]

Starting MySQL: [ OK ]

[1]+ Done mysqld_safe --skip-grant-tables

[root@servert1 ~]# mysql -u root -p

Enter password: 

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.0.77 Source distribution



Type 'help;' or '\h' for help. Type '\c' to clear the buffer.



mysql>

 

skip column names from mysql select query result

Using the "--skip-column-names" option will remove the column header from the mysql select query results.


Usage: mysql --skip-column-names -u username ....

Monitor Main Memory usage in Linux

$ cat /proc/meminfo

$ top

cardinality

Relationships between data tables define cardinality when explaining how each table links to another.
In the relational model, tables can be related as any of: many-to-many, many-to-one (rev. one-to-many), or one-to-one. This is said to be the cardinality of a given table in relation to another.

List all groups and users in Linux

cat /etc/groups  -- Lists all the users in the current system
cat /etc/passwd -- Lists all users