[root@oracleasm1 ~]# rpm -Uvh mysql80-community-release-el6-3.noarch.rpm warning: mysql80-community-release-el6-3.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ########################################### [100%] 1:mysql80-community-relea########################################### [100%] You have new mail in /var/spool/mail/root [root@oracleasm1 ~]# chkconfig mysqld on [root@oracleasm1 ~]# mysqladmin --version mysqladmin Ver 8.42 Distrib 5.1.71, for redhat-linux-gnu on i686 [root@oracleasm1 ~]# service mysqld status mysqld is stopped [root@oracleasm1 ~]# service mysqld start Initializing MySQL database: Installing MySQL system tables... OK Filling help tables... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h oracleasm1.localdomain password 'new-password' Alternatively you can run: /usr/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd /usr ; /usr/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd /usr/mysql-test ; perl mysql-test-run.pl Please report any problems with the /usr/bin/mysqlbug script! [ OK ] Starting mysqld: [ OK ] [root@oracleasm1 ~]#
If you have never set a root password for MySQL, the
server does not require a password at all for connecting as root. To set up a
root password for the first time, use the mysqladmin command at the shell
prompt as follows:
[root@oracleasm1 ~]# /usr/bin/mysqladmin -u root -h oracleasm1.localdomain password 'mysql'
Connect, Create, Switch and View All MySQL Databases
[root@oracleasm1 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.1.71 Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE DATABASE nexaprod ; Query OK, 1 row affected (0.01 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | nexaprod | | test | | tutorial_database | +--------------------+ 5 rows in set (0.00 sec) mysql>
Switch database
mysql> use nexaprod Database changed -h is used to specify a host name (if the server is located on another machine; if it isn’t, just omit it) -u mentions the user -p specifies that you want to input a password.
Create User, Tables and Manage Privilege
-- Create the MySQL User mysql> CREATE USER 'connect1'@'localhost' IDENTIFIED BY 'new_passwd'; Query OK, 0 rows affected (0.00 sec) -- View a List of MySQL Users mysql> SELECT User,Host FROM mysql.user; +-----------+------------------------+ | User | Host | +-----------+------------------------+ | root | 127.0.0.1 | | | localhost | | root | localhost | | connect1 | localhost | | connect2 | localhost | | | oracleasm1.localdomain | | root | oracleasm1.localdomain | +-----------+------------------------+ 6 rows in set (0.00 sec)
-- Connect using newly create user [root@oracleasm1 ~]# mysql -u 'connect1' --database nexprod -p Enter password: Welcome to the MySQL monitor. mysql> -- Create table mysql> CREATE TABLE users ( id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_example primary key (id) ); Query OK, 0 rows affected (0.61 sec) mysql> INSERT INTO users ( id, name ) VALUES ( 01, 'Nikhil' ); Query OK, 1 row affected (0.00 sec)
Note : When adding users within the MySQL shell in this tutorial, we will specify the user’s host as localhost and not the server’s IP address. localhost is a hostname which means “this computer,” and MySQL treats this particular hostname specially: when a user with that host logs into MySQL it will attempt to connect to the local server by using a Unix socket file. Thus, localhost is typically used when you plan to connect by SSHing into your server or when you’re running the local mysql client to connect to the local MySQL server.
[root@oracleasm1 ~]# mysql -u 'connect1' --database nexaprod -p Enter password: ERROR 1044 (42000): Access denied for user 'connect1'@'localhost' to database 'nexaprod'
Therefore, the first thing to do is to provide the user with access to the information, they will need.
mysql> GRANT ALL PRIVILEGES ON nexaprod.* TO 'connect1'@'localhost'; Query OK, 0 rows affected (0.01 sec) mysql> GRANT ALL PRIVILEGES ON nexaprod.users TO 'connect1'@'localhost'; Query OK, 0 rows affected (0.08 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) -- List Privileges mysql> show grants for 'connect1'@'localhost'; +-----------------------------------------------------------------------------------------------------------------+ | Grants for connect1@localhost | +-----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'connect1'@'localhost' IDENTIFIED BY PASSWORD '*9AAC034BCF47864DA01A23844FA65F8B6D23E10E' | | GRANT ALL PRIVILEGES ON `nexaprod`.`users` TO 'connect1'@'localhost' | | GRANT ALL PRIVILEGES ON `nexaprod`.* TO 'connect1'@'localhost' | +-----------------------------------------------------------------------------------------------------------------+
Note : Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.
Exporting Table Contents or Definitions in SQL Format
Where, nexaprod is the DB name and users is the tablename
[root@oracleasm1 ~]# mysqldump -u root -p nexaprod users > dump.txt Enter password: -- This will a create file having content as shown below. -- Table structure for table `users` --
DROP TABLE IF EXISTS `users`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `users` ( `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `users` -- LOCK TABLES `users` WRITE; /*!40000 ALTER TABLE `users` DISABLE KEYS */; INSERT INTO `users` VALUES (1,'Nikhil'); /*!40000 ALTER TABLE `users` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
Find the location of .cnf file.
# mysql --help | grep cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
In this tutorial, you have learned Installation, Creation of Database, and about some basic commands to perform several tasks in MySQL, including:
- How to install MySQL Database and configuration files.
- How to create MySQL Database and list/switch databases.
- How to create MySQL user and grant it all privileges
- How to view what privileges a MySQL user already has
No comments:
Post a Comment