Usage:
|
1 2 |
mysql> revoke PRIVILEGES [, GRANT OPTION] from 'USERNAME'@'SERVER';
mysql> flush privileges; |
Example:
|
1 2 |
mysql> revoke all privileges, grant option from 'comsiteuatusr'@'10.135.7.114';
Query OK, 0 rows affected (0.00 sec) |
|
1 2 |
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec) |
See Also
While installing MySQL Server it is always good to keep in mind that the logs and data folders will potentially have a big size. By default MySQL keeps them in the root mount point (i.e. ‘/’). That may cause your database server system disk to get full, which is never a good idea.
This article describes how to move these two folders to ‘/home’ which is ideally mounted into another disk and has enough space to keep your database data and logs.
First, I install the required apt-get packages as follows:
|
1 2 |
apt-get update
apt-get install mysql-server |
To check the status:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
/etc/init.d/mysql status
/usr/bin/mysqladmin Ver 8.41 Distrib 5.0.51a, for debian-linux-gnu on i486
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 5.0.51a-24
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 3 sec
Threads: 1 Questions: 78 Slow queries: 0 Opens: 23 Flush tables: 1
Open tables: 17 Queries per second avg: 26.000. |
Now, stop MySQL, move the folders to the right location, reconfigure MySQL and start again:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
# Stop MySQL
/etc/init.d/mysql stop
# Move and reconfigure data
mkdir /home/mysql
mv /var/lib/mysql /home/mysql/mysql-data
ln -s /home/mysql/mysql-data/ /var/lib/mysql
# Move and reconfigure logs
mv /var/log/mysql/ /home/mysql/mysql-logs
ln -s /home/mysql/mysql-logs/ /var/log/mysql
# Start MySQL and check that everything is OK
/etc/init.d/mysql start
/etc/init.d/mysql status
/usr/bin/mysqladmin Ver 8.41 Distrib 5.0.51a, for debian-linux-gnu on i486
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 5.0.51a-24
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 14 sec
Threads: 1 Questions: 78 Slow queries: 0 Opens: 23 Flush tables: 1
Open tables: 17 Queries per second avg: 5.571. |
These are some settings that I usually put on the /etc/mysql/my.cnf configuration file:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# Here you can see queries with especially long duration
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes
# A server-id unique
server-id = 177
log-bin = /var/log/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/mysql-bin.log
innodb_file_per_table
# Unique log names (this prevents replication breaking upon hostname change :-)
relay-log = iamalsounique98127-relay-bin
relay-log-index = iamalsounique98127-relay-bin
# Taking care of the auto-increment values (for multi-master replication)
auto_increment_increment = 10
auto_increment_offset = 1 |
For these changes to take effect, you would need to restart MySQL:
|
1 |
/etc/init.d/mysql restart |
If you want to ignore databases or tables you may use the following options:
|
1 2 3 4 5 6 7 8 |
binlog_ignore_db = information_schema
replicate_ignore_db = information_schema
binlog_ignore_db = mysql
replicate_ignore_db = mysql
# Ignore all the cache* tables which have caused DUPLICATE
# ENTRY issues. Unai.
replicate_wild_ignore_table = exampledb.cache% |
Having ‘binlog_ignore_db’ is enough to exclude databases from replication BUT having ‘replicate_ignore_db’ as well will make things clearer since the databases that are being ignored will appear in both the ‘SHOW SLAVE STATUS\G’ and ‘SHOW MASTER STATUS\G’.