Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
10 Newer Entries Showing entries 61 to 70 of 33490 10 Older Entries
Setting Up mysql_profile
+0 Vote Up -0Vote Down

To make life easier, this profile needs to be created on every MySQL server we log on.


Connect as root and create the following file with correct locations.



cd /


touch /mysql_profile


chmod 600 /mysql_profile


vi /mysql_profile



export ERRORLOG=/local/db/mysql      

export DATADIR=/local/db/mysql      

export MYCNF=/etc


export …

  [Read more...]
Check / Revoke access for a user
+0 Vote Up -0Vote Down



To see which grants a user has do


show grants for sdas;



| Grants for …

  [Read more...]
Taking consistent mysql dumps
+0 Vote Up -0Vote Down

/usr/local/bin/mysqldump -uroot --p*** --databases prequal --lock-all-tables | gzip) > /home/backup/rebuild_prequal-`hostname -s`.sql.gz 2> /home/backup/rebuild_prequal-`hostname -s`.sql.err.log


I kept an eye on the database, and it queued all of the inserts until the backup finished, but allowed the selects to go through, an example of processlist is attached. Maybe we should consider this for some of the smaller databases and slaves


+0 Vote Up -0Vote Down

Apart from the start/stop commands the below commands show the different commands that can also be used to start and stop mysql daemon especially if you have encountered the following errors when you were using the 'normal' start/stop commands:


ERROR! MySQL manager or server PID file could not be found!

Starting MySQL.................................. ERROR


1: On the server Search for mysqld_safe 

cd /

find . -name mysqld_safe  -print



(ie found : …

  [Read more...]
MySQL On Demand Web Seminars
+0 Vote Up -0Vote Down

Error 134
+0 Vote Up -0Vote Down

090326 12:30:54 [ERROR] Got error 134 when reading table './ANYPROV/ORDERS'


Reference from Mysql Bug Database



perror 134
and get this result:
Unknown error 134
Record was already deleted (or record file crashed)




mysql> check table ORDERS;


| …

  [Read more...]
Example of daily deletion of a table via crontab
+0 Vote Up -0Vote Down

00 08 * * * mysql -uroot -p*** -se 'delete from tacacs.accounting where date < date_sub(now(), interval 90 day);'  > /tmp/delete_accounting.log 2>&1

Deleting master logs script ONLY after checking slaves dont need them
+0 Vote Up -0Vote Down


You can use this script to purge master logs older than the day of the current log.  It checks the slaves are not using the older logs first. 



Some prechecks:


1) create a repl_client user on each slave - i.e


GRANT REPLICATION CLIENT ON *.* TO 'repl_client'@'mk-dbxx-1' IDENTIFIED BY 'r3pl';


2) Put the master and slave user, pwd and hosts in the section below.


3) Set savedays to number of days of logs to keep if more than today+1.


4) Make …

  [Read more...]
Deleting master logs older than one day in a single command
+0 Vote Up -0Vote Down

mysql -uroot –p*** -e 'show master logs; purge master logs before date_sub(now(), interval 1 day); show master logs;' 

sample output




| Log_name         | File_size  |


| mysql-bin.000325 | 1073741878 |

| mysql-bin.000326 | 532809491  |



| Log_name         | …

  [Read more...]
Moving the whole datamysql base to a new location
+0 Vote Up -0Vote Down

Occasionally, mysql is installed in the wrong filesystem, and as the data build, the filesystem runs out of space. Moving the database is relatively straight forward


1.      Ensure there is a good, recent backup. Take an extra backup to be on the safe side


2.      Decide where the files will be moved to and create any subdirectories as required. Ensure the permissions are correct


            cd /local/mysql

  [Read more...]
10 Newer Entries Showing entries 61 to 70 of 33490 10 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.