How to design Users, Roles and permission schema

For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:

RENAME TABLE old_db.table TO new_db.table;

You will need to adjust the permissions after that.

For scripting in a shell, you can use either of the following:

mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \ 
    do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done

OR

for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;

Notes:

• There is no space between the option -p and the password. If your database has no password, remove the -u username -ppassword part.

• If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema error). If that is the case, use a traditional way to clone a database and then drop the old one:

  mysqldump old_db | mysql new_db

• If you have stored procedures, you can copy them afterwards:

  mysqldump -R old_db | mysql new_db

Your command does not work because the redirection is performed by your shell which does not have the permission to write to /root/test.out. The redirection of the output is not performed by sudo.

There are multiple solutions:

• Run a shell with sudo and give the command to it by using the -c option:

  sudo sh -c 'ls -hal /root/ > /root/test.out'
  

• Create a script with your commands and run that script with sudo:

  #!/bin/sh
  ls -hal /root/ > /root/test.out
  

  Run sudo ls.sh. See Steve Bennett's answer if you don't want to create a temporary file.

• Launch a shell with sudo -s then run your commands:

  [nobody@so]$ sudo -s
  [root@so]# ls -hal /root/ > /root/test.out
  [root@so]# ^D
  [nobody@so]$
  

• Use sudo tee (if you have to escape a lot when using the -c option):

  sudo ls -hal /root/ | sudo tee /root/test.out > /dev/null
  

  The redirect to /dev/null is needed to stop tee from outputting to the screen. To append instead of overwriting the output file (>>), use tee -a or tee --append (the last one is specific to GNU coreutils).

Thanks go to Jd, Adam J. Forster and Johnathan for the second, third and fourth solutions.