Set autoincrement value in mysql
Sometimes it is necessary to set the starting value of MySQL auto-increment value. This can be set as
alter table table_name auto_increment=XXXX
where table_name is the name of the table and XXXX is the starting value of auto increment
MySQL export/import database
The easiest way to export is use syntax in command prompt (cmd):
mysqldump -u USER -p PASSWORD DATABASE > filename.sql
To import the same file, the syntax is:
mysql -u USER -p PASSWORD DATABASE < filename.sql
MySQL server has gone away
While importing a mysql dump file, I was receiving an error “MySQL server has gone away”. The reason is that the maximum allowed packet size was less than the dump file size.
Edit “my.ini” file and update the value for
max_allowed_packet = XXM
where XX is the maximum size.
This should fix the issue.
Recover MySQL root password
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 root password
Step # 5: Exit and restart 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
Output:
Stopping MySQL database server: mysqld.
Step # 2: Start to MySQL server w/o password:
# mysqld_safe --skip-grant-tables &
Output:
[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started
Step # 3: Connect to mysql server using mysql client:
# 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>
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;
mysql> quit
Step # 5: Stop MySQL Server:
# /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
Step # 6: Start MySQL server and test it
# /etc/init.d/mysql start
# mysql -u root -p
