Brain's Workbench
You can use the following script to dump mysql database and can add to crontab entry for daily backup!
#!/bin/sh
DAY=`/bin/date +%Y%m%d`
TFILE="/backup/MYDB.$DAY.gz"
mysqldump -u root -p'MYPASSWORD' MYDB | gzip > $TFILE
echo "cache dump completed, dump script by rumi (hasan.emdad@mango.com.bd)"
First things first. Log in as root and stop the mysql daemon. Now lets start up the mysql daemon and skip the grant tables which store the passwords.
mysqld_safe –skip-grant-tables
You should see mysqld start up successfully. If not, well you have bigger issues. Now you should be able to connect to mysql without a password.
mysql –user=root mysql
update user set Password=PASSWORD('new-password') where user='root';
flush privileges;
exit;
Now kill your running mysqld, then restart it normally. You should be good to go. Try not to forget your password again.
Scenario Master – Master replication
MasterA is a client facing server
MasterB is a warm standby server (read only)
MasterB restarted abruptly and when instances were braught back up MasterA (it’s slave) was showing the following error:
MasterA has the following error in show slave status:
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’
Solution:
Slave: stop slave;
Master: flush logs
Master: show master status; — take note of the master log file and master log position
Slave: CHANGE MASTER TO MASTER_LOG_FILE=’log-bin.00000X′, MASTER_LOG_POS=106;
Slave: start slave;
Read more: http://mysqlpreacher.com/wordpress/2010/12/could-not-find-first-log-file-name-in-binary-log-index-file/#ixzz2ILRnl4XI
Src: http://mysqlpreacher.com/wordpress/2010/12/could-not-find-first-log-file-name-in-binary-log-index-file/
The pv command is one that I really enjoy using but it's also one that I often forget about. You can't get a much more concise definition of what pv does than this one:
pv allows a user to see the progress of data through a pipeline, by giving information such as time elapsed, percentage completed (with progress bar), current throughput rate, total data transferred, and ETA.
The usage certainly isn't complicated:
To use it, insert it in a pipeline between two processes, with the appropriate options. Its standard input will be passed through to its standard output and progress will be shown on standard error.
A great application of pv is when you're restoring large amounts of data into MySQL, especially if you're restoring data under duress due to an accidentally-dropped table or database. (Who hasn't been there before?) The standard way of restoring data is something we're all familiar with:
# mysql my_database < database_backup.sql
The downside of this method is that you have no idea how quickly your restore is working or when it might be done. You could always open another terminal to monitor the tables and databases as they're created, but that can be hard to follow.
Toss in pv and that problem is solved:
# pv database_backup.sql | mysql my_database
96.8MB 0:00:17 [5.51MB/s] [==> ] 11% ETA 0:02:10
When it comes to MySQL, your restore rate is going to be different based on some different factors, so the ETA might not be entirely accurate.
Src: http://rackerhacker.com/2010/11/24/monitor-mysql-restore-progress-with-pv/
MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. Within seconds, it will display statistics about your MySQL installation and the areas where it can be improved.
It's key to remember that MySQLTuner is a script which can assist you with your server, but it is not the solution to a badly performing MySQL server. The best performance gains come from a thorough review of the queries sent to the server, and an evaluation of the MySQL server itself. A qualified developer in your application's programming or scripting language should be able to work with a MySQL database administrator to find improvements for your server. Once the server and application are optimized well, you may need to consider hardware upgrades to the physical server itself.
This is a really useful tool for helping to optimize MySQL performance.Understanding the various my.cnf variables and how they affect performance can seem really complicated but this tool takes some of the pain away and makes it easier to understand the effects of each variable.It is especially useful to be able to see the global memory usage, memory usage per thread and the maximum possible memory usage — that is really valuable information that is otherwise complex to calculate.
Run MySQLTuner in Debian
Copy and past the below code and save it as mysqltuner.pl using nano 😉
Setting Up The Two Base Systems
In this setup there will be one master server (which runs the ISPConfig control panel interface) and one slave server which mirrors the web (apache), email (postfix and dovecot) and database (MySQL) services of the master server.
To install the clustered setup, we need two servers with a Debian 6.0 minimal install.
Install only steps 1 – 8 of the perfect server tutorial and not the other steps as they differ for a clustered setup!
In my example I use the following hostnames and IP addresses for the two servers:
Master Server
Hostname: server1.example.tld
IP-address: 192.168.0.105
Slave server
Hostname: server2.example.tld
IP-address: 192.168.0.106
Whereever these hostnames or IP addresses occur in the next installation steps you will have to change them to match the IP's and hostnames of your servers.
It is a good idea to synchronize the system clock with an NTP (network time protocol) server over the Internet. Simply run
The syntax is as follows to import the data created by mysqldump command:
mysql -u {DB-USER-NAME} -p {DB-NAME} < {db.file.sql} mysql -u {DB-USER-NAME} -h {MySQL-SERVER-HOST-NAME} -p {DB-NAME} < {db.file.sql}
In this example import a file called sales.sql for salesdb1 user and sales db, enter:
$ mysql -u sales -p salesdb1 < sales.sql
If database salesdb1 does not exists, first create it and than import it as follows:
$ mysql -u root -p -e 'create database salesdb1'
$ mysql -u sales -p salesdb1 < sales.sql
Selecting a database:
mysql> USE database;
Listing databases:
mysql> SHOW DATABASES;
Listing tables in a db:
mysql> SHOW TABLES;
Describing the format of a table:
mysql> DESCRIBE table;
Creating a database:
mysql> CREATE DATABASE db_name;
Creating a table:
mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));
Ex: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);
Load tab-delimited data into a table:
mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name;
(Use \n for NULL)
Inserting one row at a time:
mysql> INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31');
(Use NULL for NULL)
Retrieving information (general):
mysql> SELECT from_columns FROM table WHERE conditions;
All values: SELECT * FROM table;
Some values: SELECT * FROM table WHERE rec_name = "value";
Multiple critera: SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";
Reloading a new data set into existing table:
mysql> SET AUTOCOMMIT=1; # used for quick recreation of table
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table;
Fixing all records with a certain value:
mysql> UPDATE table SET column_name = "new_value" WHERE record_name = "value";
Selecting specific columns:
mysql> SELECT column_name FROM table;
Retrieving unique output records:
mysql> SELECT DISTINCT column_name FROM table;
Sorting:
mysql> SELECT col1, col2 FROM table ORDER BY col2;
Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;
You can recover MySQL database server password with following five easy steps.
Step # 1: Stop the MySQL server process.
Step # 2: Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for password.
Step # 3: Connect to mysql server as the root user.
Step # 4: Setup new mysql root account password i.e. reset mysql password.
Step # 5: Exit and restart the MySQL server.
Here are commands you need to type for each step (login as the root user):
# /etc/init.d/mysql stop
Output:
Stopping MySQL database server: mysqld.
# mysqld_safe --skip-grant-tables &
Output:
[1] 5988 Starting mysqld daemon with databases from /var/lib/mysql mysqld_safe[6025]: started
# mysql -u root
Output:
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
Output:
Stopping MySQL database server: mysqld STOPPING server from pid file /var/run/mysqld/mysqld.pid mysqld_safe[6186]: ended [1]+ Done mysqld_safe --skip-grant-tables
# /etc/init.d/mysql start
# mysql -u root -p
By default, MySQL does not allow remote clients to connect to the MySQL database.
If you try to connect to a remote MySQL database from your client system, you will get “ERROR 1130: Host is not allowed to connect to this MySQL server” message as shown below.
$ mysql -h 192.168.1.8 -u root -p Enter password: ERROR 1130: Host '192.168.1.4' is not allowed to connect to this MySQL server
You can also validate this by doing telnet to 3306 mysql port as shown below, which will also give the same “host is not allowed to connect to this mysql server” error message as shown below.