MYSQLDump Only Certain Rows of Table

How to use mysqldump for a portion of a table ?

below are command to use mysqldump for a portion of a table :-

>mysqldump --opt --user=username --password=password databasename tablename 
--where=date_pulled='2011-05-23' > test.sql

Just fix your --where option. It should be a valid SQL WHERE clause, like:

--where="date_pulled='2011-05-23'"


You have the column name outside of the quotes.

One more Example :-

>mysqldump -uroot -p -h172.16.101.5 databasename tablename --where=" id<100000> data_path.sql

OR

you can use below also

>SELECT * INTO OUTFILE 'data_path.sql' from tablename where id<100000 p="">

Full Table MysqlDump :-

mysqldump -u root -p db_name table_name > table_name.sql

Read more...

Disable MySQL slave Configuration and Reset Mysql Slave

How do I completely disable MySQL replication ?

Reset Mysql Slave :- 

following are steps to reset mysql slave :-

1. >login mysql

2. mysql>check slave status \G;

3. mysql> STOP SLAVE;

4. mysql> RESET SLAVE;
               (Use RESET SLAVE ALL; for MySQL 5.5.16 and later)

5. mysql>exit

6. edit my.cnf file
    vi /etc/my.cnf

7. comment below in my.cnf
     #relay-log=mysqld-relay-bin
     #replicate-do-db=
     #replicate-do-db=
     #server-id=2

8. restart mysql

    /etc/init.d/mysqld restart

Read more...

Kill User Session

how do I kill another Login User Session remotely - Linux, Centos

How do I kill all login users session in Linux  OS using command prompt ?

use "pkill" command which will look up or signal processes based on name. It can send the specified signal (such as KILL) to each process. following commands as root user:-

$ sudo pkill -9 -u username


OR
# pkill -9 -u username

To list all users pids, enter:
$ pgrep -u username

OR better try:
$ ps -fp $(pgrep -d, -u userNameHere)

Example: Kill Unix / Linux User Session :-

In this example, list all process owned by a user called lighttpd, enter:

# ps -fp $(pgrep -d, -u lighttpd)

Sample outputs:

UID        PID  PPID  C STIME TTY          TIME CMD

lighttpd   4703     1  0 04:20 ?        00:01:07 /usr/sbin/lighttpd -f /user/local/etc/.myconf/lighttpd/master.example.com.conf

lighttpd   4705  4703  0 04:20 ?        00:00:00 /usr/bin/php-cgi

lighttpd   4708  4703  0 04:20 ?        00:00:00 /usr/bin/php-cgi

lighttpd   4710  4703  0 04:20 ?        00:00:00 /usr/bin/php-cgi

lighttpd   4712  4703  0 04:20 ?        00:00:00 /usr/bin/php-cgi

lighttpd   4714  4703  0 04:20 ?        00:00:00 /usr/bin/php-cgi

lighttpd   4715  4703  0 04:20 ?        00:00:00 /usr/bin/php-cgi

lighttpd   4716  4710  0 04:20 ?        00:00:07 /usr/bin/php-cgi

lighttpd   4718  4705  0 04:20 ?        00:00:00 /usr/bin/php-cgi

lighttpd   4719  4708  0 04:20 ?        00:00:02 /usr/bin/php-cgi

To kill all process owned by lighttpd user, enter:

# pkill -9 -u lighttpd


Task: Kill and Logout All Users

The ultimate command to kill and logout all users is as follows:
# skill -KILL -v /dev/pts/*

Read more...

MySQL error 1062 - Duplicate entry when starting slave or replication fail?

Duplicate entry when starting slave? MySQL error 1062

Why does replication fail with error Duplicate entry for key?

following error when executing the mysql> start slave;:

    Last_SQL_Errno: 1062

    Last_SQL_Error: Error 'Duplicate entry '115846' for key

'PRIMARY'' on query. Default database: 'db'. Query: 'INSERT INTO

request_posted (id, user_id, channel, message, link, picture, name, ...

Solution :-

Try to skip some queries with:

mysql> STOP SLAVE; 
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 
mysql> START SLAVE;

Read more...

Cannot load from mysql.proc. The table is probably corrupted

Following from my dependency hell problems the other day my new version of MySQL/MariaDB seems to have all its stored procedures corrupted, displaying the following message when any of them are executed:

Cannot load from mysql.proc. The table is probably corrupted

also "your MariaDB server version for the right syntax to use near 'mysql_upgrade' at line 1"

how to fix it ? run below command to fix it.

> mysql_upgrade  -uroot -p

Problem solved.

Read more...