You can use the following query to get all duplicated records of a MySQL table having equal values for the respective field:
|
1 |
SELECT fieldname, COUNT(*) FROM tablename GROUP BY fieldname HAVING COUNT(*) > 1 |
This query will give you a list of all the entries having the same value for fieldname. It also will show the amount of duplicates.
To get information about the currently connected user and more MySQL status information, you can call the following command if you are already logged in:
|
1 |
mysql> STATUS; |
This will give you an output like that:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
-------------- mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1 Connection id: 1044395 Current database: Current user: niceguy@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.1.73-log Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 2 days 6 hours 26 min 44 sec Threads: 8 Questions: 399680642 Slow queries: 20292 Opens: 9697 Flush tables: 1 Open tables: 4096 Queries per second avg: 2039.145 -------------- |
As you can see, you got a lot more information like operation system data, MysSQL version or the number of slow […]
You can see all running queries in live time with:
|
1 |
$> mysqladmin -u dbuser -p123 -i 2 processlist |
The option -i determines the number of seconds between refreshes(alias –sleep=DELAY).
To check and optimize tables of a MySQL database:
|
1 |
$> mysqlcheck -os <db_name> -u<username> -p |
To achieve the same for all databases of the respective MySQL server you can run the following command:
|
1 |
$> mysqlcheck -Aos -u<username> -p |
Further information about check and optimize tables in MySQL To get a few more facts about how to check and optimize tables in MySQL, you should […]
To find out where the MySQL configuration (my.cnf) is stored, you can use the strace command:
|
1 |
$> strace mysql ";" 2>&1 | grep my.cnf |
You will get an output showing all the paths the MySQL deamon searches for the my.cnf upon startup like that:
|
1 2 3 4 5 |
stat("/etc/my.cnf", 0x7fffecb75210) = -1 ENOENT (No such file or directory) stat("/etc/mysql/my.cnf", {st_mode=S_IFREG|0644, st_size=3505, ...}) = 0 open("/etc/mysql/my.cnf", O_RDONLY) = 3 stat("/usr/etc/my.cnf", 0x7fffecb75210) = -1 ENOENT (No such file or directory) stat("/home/sheldon/.my.cnf", 0x7fffecb75210) = -1 ENOENT (No such file or directory) |
As you can see, the service searches the following files: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf /home/sheldon/.my.cnf But only the /etc/mysql/my.cnf is found […]
mytop is an mysql tool written in Perl to look at the database queries during runtime. This is how to debug queries with mytop. Installation
|
1 |
apt-get install mytop |
Usage
|
1 |
mytop -u USERNAME -pPASSWORD -d DATABASE |
