115

How do I create a user with the same privileges as root in MySQL/MariaDB?.

tintinve
  • 87
  • 12
srinivasankanna
  • 1,333
  • 2
  • 9
  • 9
  • 2
    try to google it: http://dev.mysql.com/doc/refman/5.1/en/adding-users.html – Dinesh May 25 '13 at 06:29
  • Related question: http://stackoverflow.com/questions/5016505/mysql-grant-all-privileges-on-database – User Aug 17 '14 at 02:28

1 Answers1

192
% mysql --user=root mysql
CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' WITH GRANT OPTION;
CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' WITH GRANT OPTION;
CREATE USER 'admin'@'localhost';
GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
CREATE USER 'dummy'@'localhost';
FLUSH PRIVILEGES;
Nowaker
  • 12,154
  • 4
  • 56
  • 62
imulsion
  • 8,820
  • 20
  • 54
  • 84
  • 2
    Can you expand on why you create `'localhost'` and `'%'` users? – User Aug 17 '14 at 02:29
  • 3
    The 'monty'@'localhost' account can be used only when connecting from the local host. The 'monty'@'%' account uses the '%' wildcard for the host part, so it can be used to connect from any host – takeshin Sep 26 '14 at 10:36
  • @takeshin, `%` doesn't seem to include `localhost` does it? – Pacerier Jan 14 '15 at 10:23
  • 3
    https://dev.mysql.com/doc/refman/5.5/en/adding-users.html should answer your questions. – sjas Mar 24 '15 at 20:53
  • 1
    I had to run `$ mysql -u root -p` and then enter root password to be able to start the MySQL command line – Megidd May 22 '18 at 07:13
  • I had issues with wildcare `%`, but MariaDB doc's state `If the host name is not provided, it is assumed to be '%'.` so I created a user without `@host` and one for `@localhost` and that did the trick, not sure why mariadb wouldn't take my % – FreeSoftwareServers May 04 '20 at 18:45