In this article, I just wanted to describe the very first steps that should be taken after you downloaded and installed the package to work with MySQL databases. I was not going to describe MySQL myself at all here and did not expect to go into all the technical details regarding security. If you want detailed information, read the fine manual. If you want to start making databases as soon as possible, read this little guide.
After you have installed the server and client parts of the MySQL package, the next step is to make it all work. The database daemon is started using the mysqld command. Using the [–help] key, you can view all available options. Also, this key will allow you to see the list of directories with which MySQL works.
For the package to function, you need to create a master system database called mysql . All databases are created in a separately selected folder, which can be calculated using mysqld –help . Find after a long list of possible keys lines, clearly pointing to different directories.
basedir : speaks for itself – this is the base directory relative to which others can be set.
datadir : all databases will be stored in it.
If you installed MySQl using RPM packages or some other automated method, it is possible that a system database already exists in this directory. If you compiled a package or copied executable files manually, then it may not exist or it is empty. In this case, the mysql database must be created using the mysql_install_db script . If there are no errors, then after the script ends, it will ask you to set a password for the root user. What does it mean?
Databases in MySQL, as well as in many other systems, are available simultaneously to a large number of users who can connect to the MySQL server both from the local computer, via server languages and CGI, and via TCP / IP through MySQL clients located on remote computers. After creation, in the system database will be described including privileges for different users. The most important of them, of course, is the root user, who has full access to all databases. You need to set a password for it, since by default it does not exist.
shell> mysql -u root mysql
This means starting the main MySQL client named mysql as the root user (-u root) and selecting the mysql database. Next, the mysql program console opens. We make the most common SQL query:
mysql> UPDATE user SET Password = PASSWORD (‘new_password’) WHERE user = ‘root’;
This will update the Password fields for the user table, with user = ‘root’ fields. In other words, the new_password password encrypted using the PASSWORD () method will be set for the root user.
Making MySQL accept the changes:
mysql> FLUSH PRIVILEGES;
There is another way that works on MySQL versions> = 3.22:
mysql> SET PASSWORD FOR root = PASSWORD (‘new_password’);
or even from the shell using the mysqladmin program:
shell> mysqladmin -u root password new_password
Everything, now root cannot just enter into the mysql program. We write
and see this:
shell> mysql -u root
ERROR 1045: Access denied for user: ‘root @ localhost’ (Using password: NO)
You need to log in with the password like this:
shell> mysql -u root -p
Enter password: *******
That’s all. The root password does not have to be the same as its password on the system.
If the password was accidentally forgotten, to set it by new one, you will have to erase the files mysql.frm mysql.MYI and mysql.MYD from the database folder, then run the script mysql_install_db and repeat everything on the new one.
If you are interested in the structure of the system database, you can build it using SQL queries from the mysql program, as well as using internal commands and the mysqlshow utility. for example
shell> mysqlshow some_database
will show a list of all the tables in the database some_database, and the query
mysql> SELECT * FROM some_table;
will display the contents of some_table in a table format
ok. Now it is good to add database users, along with their rights and passwords.
Use the expression GRANT. You can again make direct corrections to the mysql tables, but this will be too long. So:
mysql> GRANT ALL PRIVILEGES ON *. * TO admin @ localhost IDENTIFIED BY ‘some_password’ WITH GRANT OPTION;
This will create an admin user who can do whatever he wants with all databases and generally mysql, connecting only from localhost and specifying the password some_password. To admin can connect from other hosts, you must add the line
mysql> GRANT ALL PRIVILEGES ON *. * TO admin @ “%” IDENTIFIED BY ‘some_password’ WITH GRANT OPTION;
By the way *. * Means to which databases and tables admin has access. Designations are made as follows “base.table”
To create a more or less advanced user, you can use the following expression:
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, CREATE, DROP ON *. * TO poweruser @ localhost IDENTIFIED BY ‘user_password’;
Such a user will be able to use all the basic SQL commands for the data in the tables, as well as create and delete databases. However, it will not be able to shut down, restart the MySQL daemon, look at the process list, will not have access to the server files, and will also be able to connect to the database only from localhost and specifying its password.
Here are all possible options for privileges:
SELECT, INSERT, UPDATE, DELETE – similar sql-commands for data
operations INDEX – operations with indexes in tables
REFERENCES – work with links in databases and tables
CREATE, DROP – create and delete databases and tables
GRANT, ALTER – perform operations privileges
RELOAD, SHUTDOWN, PROCESS – control the server mysql. Restart, kill and see all connections accordingly. More precisely, this gives the right to execute the commands of the mysqladmin program aimed at fulfilling the specified goals.
FILE – allows you to drive into the database any readable file from the server
Choose which users to create. For a multi-user server, you can advise you to make users who can only change the data of one database. If one webmaster manages the entire server, it is quite possible to provide him with a wider freedom of action.
Fine! Now the minimum of what you may need from the MySQL server is configured and you can start creating tables and contribute data.
And do not forget to add mysqld to autorun.
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Recent Posts: TechnoBlogy