Error : “The user specified as a definer does not exist” when importing mysqldump

                      Change the Definer of Mysql Stored Procedures

Sometimes when we try to import mysqldump into our local machine through command line terminal,we end up getting following error

mysqldump: Got error: 1449: The user specified as a definer ('user@%') does not exist when using LOCK TABLES

This error comes because the database we are trying to import contains procedures and functions that are defined by the user 'user'.

You can monitor your procedure characteristics like database name,type,creator,creation time and character set information by using this command:

         SHOW procedure status;

after executing this command, you can see some procedures creator name is ('user@%').

So you need to change it to 'root@%' or 'root'@'localhost',you should update definer for procedures using this command in Mysql terminal,

         UPDATE 'mysql'.'proc' p SET definer='root@%' WHERE definer='user@%';

keep in mind one thing this command will update definer for all procedures.

Other way to achieve this is create user name 'user' in your database and grant all permission to it.

        GRANT all on *.* to 'user@%' identified by 'password' with grant option;

You can also view grants on any user using this command,

        SHOW GRANTS for 'user@%';




I hope this solution helps you.Enjoy coding :)

Execute mysql procedure from command line

                      Execute mysql procedure from command line

To call mysql stored proceudres from command line,first login into mysql using this command

     mysql –u[username] –p[password] database_name then,

Suppose your procedure name is countNoOfRows(),use this command

      call countNoOfRows();

To call parameterized stored procedure,

     call countNoOfRows(1,’myRow’);

To execute stored procedure and prints its output in txt file,open command line and use this command, suppose your procedure name is “calculate_raw_data()”

 mysql -uroot -prootDB nos -e "call calculate_raw_data('2,3','23')" >/exports/satish/output.txt

It will print the output in ouput.txt file which gets created in location >/exports/satish/.

This can be used when you are trying to debug your mysql stored procedure. You can print output at each line and monitor afterwards what’s exactly happening in your stored procedure.

As in many Linux distributions mysql gui application doesn’t work, above command can be useful for debugging purpose.

 

Mysql Backup and Restore command for linux

Backup MySQL DB
In order to take a backup of MySQL database,first you need to make sure that required database is present in database server and you have a required access to it.

The following command is used to take an backup:

mysqldump -u[username] -p[password] [database name] > [file name.sql]

for ex: mysqldump -uroot -prootDB mydb>/exports/db/mysql.sql


If you want to take an backup of all databases present in database server,then use following command:

mysqldump -uroot -prootDB --all-databases>/exports/db/allmysql.sql


To take a backup of mysql procedures ,functions along with tables,use this command:

mysqldump -uroot -prootDB --routines mydb>/exports/db/mysql.sql

Restore MySQL DB

To restore the database use this command:

mysql -u[username] -p[password] [database name] < [file name.sql]

for ex: mysql -uroot -prootDB mydb</exports/db/mysql.sql



In case of any doubt,please post a question.