- You can add –max_allowed_packet=512M to your mysqldump command.
- Or add max_allowed_packet=512M to [mysqldump] section of your my.cnf
Note: it will not work if it is not under the [mysqldump] section…
Few small hack will ease you importing large sized mysql dump in a bit faster process. The trick worked for me for a 70 GB dumped sql, can’t guarantee it’d work for you!
need to change the following:
innodb_buffer_pool_size = 4G innodb_log_buffer_size = 256M innodb_log_file_size = 1G innodb_write_io_threads = 16 innodb_flush_log_at_trx_commit = 0
Why these settings ? Continue reading “MySQL my.cnf tweak for faster importing large database” »
If you forgot your MySQL root password, you can reset it by following these steps.
1. Stop the MySQL service.
service mysql stop
2. Start MySQL without password and permission checks.
mysqld_safe --skip-grant-tables &
3. Press [ENTER] again if your output is halted.
4. Connect to MySQL.
mysql -u root mysql
5. Run following commands to set a new password for root user. Substitute NEW_PASSWORD with your new password.
UPDATE user SET password=PASSWORD('NEW_PASSWORD') WHERE user='root'; FLUSH PRIVILEGES;
6. Restart the MySQL service.
service mysql restart
Use the following steps to reset a MySQL root password by using the command line interface.
Stop the MySQL service
(Ubuntu and Debian) Run the following command:
sudo /etc/init.d/mysql stop
(CentOS, Fedora, and Red Hat Enterprise Linux) Run the following command:
sudo /etc/init.d/mysqld stop
Start MySQL without a password
Run the following command. The ampersand (&) at the end of the command is required. Continue reading “Reset a MySQL root password for Debian” »
Some time we faces issues with MySQL installation on Linux machine. If we simply remove MySQL packages and re-install doesn’t fixes the issue, in that case old settings may still exists on server which again affects new install. In that case first uninstall MySQL completely from system and erase all settings of old install. To do the same follow the below settings.
Note: Please do not use below steps if MySQL have any running databases.
Step 1: Uninstall MySQL Packages
First uninstall all the MySQL packages installed on your server
# yum remove mysql mysql-server
Step 2: Romove MySQL Directory
Now we need to remove MySQL data directory from system which by default exists at/var/lib/mysql. If you didn’t find this, It may be changed to some other place, which you can find in my.cnf file with variable datadir. Delete the /var/lib/mysql directory from system but we prefer to rename it to keep a backup of existing files.
# mv /var/lib/mysql /var/lib/mysql_old_backup Continue reading “Reinstalling MySQL on CentOS/Redhat 6” »
This article is about setting up MySQL Master-Master database replication between two Cloud Servers. Master-Master data replication allows for replicated data, stored on multiple computers, to be updated by any authorized contributing member of the group. This allows for more open collaboration than Master-Slave replication where any needed changes identified by a group member must to be submitted to the designated “master” of the node.
The operating system we will use is Debian 5 (Lenny).
- We will have two Cloud Servers, named debian501 and debian502 for the purpose of this exercise. Both servers have two IP addresses (one public, one private). We will configure the replication to be done over the private IP interface so that we don’t incur any bandwidth charges.
- First we need to install MySQL on both the Debian Cloud Servers. As always, prior to installing any packages, we need to make sure that our package list is up to date and our locale/language settings are configured properly.
- Update the package database:
- Install locales (optional):
- The dpkg-reconfigure locales command will bring up a locales setting window where you can choose the locales for your system depending on your country and region. In this case we have chosen en_GB.UTF-8.
- Now, you can run the following commands to install MySQL:
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_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’
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
Selecting a database:
mysql> USE database;
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;
mysql> SELECT col1, col2 FROM table ORDER BY col2;
Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC; Continue reading “MySQL Cheat Sheet” »
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):
Step # 1 : Stop mysql service
# /etc/init.d/mysql stop
Stopping MySQL database server: mysqld.
Step # 2: Start to MySQL server w/o password:
# mysqld_safe --skip-grant-tables &
 5988 Starting mysqld daemon with databases from /var/lib/mysql mysqld_safe: started
Step # 3: Connect to mysql server using mysql client:
# mysql -u root
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>
Step # 4: Setup new MySQL root user password
mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
Step # 5: Stop MySQL Server:
# /etc/init.d/mysql stop
Stopping MySQL database server: mysqld STOPPING server from pid file /var/run/mysqld/mysqld.pid mysqld_safe: ended + Done mysqld_safe --skip-grant-tables
Step # 6: Start MySQL server and test it
# /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. Continue reading “How to Allow MySQL Client to Connect to Remote MySQL server” »