Posts

MySQL Configuration File - Updating and changing the default location of my.cnf

Image
MySQL Option file or Configuration file Most MySQL programs can read startup options from option files (also called as configuration files ). Option files provide a convenient way to specify commonly used options so that they need not be entered on the command line each time you run a program. File path: /etc/my.cnf The   mysqld --verbose --help   is used to display detailed information about the mysql server's configurations, options, and supported features. To check the contents of the my.cnf file :     sudo nano /etc/my.cnf Let's update our configuration file now. We will remove all the comments and one option of 'server-id', for which we will assign the value as 1. To check for the serer-id in MySQL you execute this command:     show variables like '%server_id%'; As you can see the server_id is 1. Now let's update this in our option file. In our configuration file we will add the following line:     server-id=1 Notice, that our system ...
Post a Comment
Read more

MySQL User Administration - Creating new users, logging in with them , and dropping users.

Image
Learn how to create a new user and explore multiple methods to connect to mysql with that user. Additionally, discover the steps to delete a user when no loner needed. Let's look at the steps to create a MySQL user: Step 1: First let's check what options are available when creating a user.     help create user; Step 2: To create user execute this command:     create user if not exists 'user_name' identified by 'password' password expire never account unlock; Step 3 : Now let's grant the permissions to the user. In this case we are granting all the privileges to the user.     grant all privileges on *.* to 'junior' with grant option; Step 4: Flush the privileges.     flush privileges;     (To reload the privileges, ensuring any changes made to the account take effect immediately) Step 5: To confirm whether all privileges are granted:     show grants for user_name; Now the question arises: how do we connect to mysql with the ...
Post a Comment
Read more

MySQL Storage Engines - Its types, InnoDB, installing and disabling storage engines.

Image
 MySQL Storage Engines The main use of storage engines in DBMS is to manage how data is stored, retrieved, manipulated within the database. It is a pluggable storage engine architecture ,i.e, you can load and unload the data while the MySQL server is running. Example of storage engines are : InnoDb, MyISAM, Federated, Memory, CSV, Blackhole etc. You can view the different types of storage engines in MySQL by executing:     show engines;      As they are plugins, they are installed under specific file path.  You can find the path of the plugins in your mysql server by executing:     show variables like 'plugin_dir'; Now, let's visit this file location and check which plugins are inside it.     ls -ltr  /usr/lib64/mysql/plugin/ Important: To use any storage engine in mysql, we need to copy its .so file, drop it to the path location stated above, and then use the install plugin command to access it. Now let's try some of the st...
Post a Comment
Read more

MySQL config editor , Executing MySQL commands from the terminal, mysqlimport and mysqlcheck

Image
We will log in to MySQL without entering credentials by using MySQL config editor, then execute mysql commands from the terminal, import the data into a mysql table, and check data integrity using mysqlcheck. mysql_config_editor is a command line utility provided by MySQL to securely manage login credentials. It can store credentials like username, password, host and port in an encrypted login file path. To learn more about this command and how to use it you can execute this query:          mysql_config_editor --help Now we will set the credentials by using mysql_config_editor:     mysql_config_editor set --user=root --password -login-path=client To verify whether the credentials are stored and encrypted :     mysql_config_editor print Now let's check whether we can log in to MySQL without providing user and password credentials.     mysql Great! We have successfully stored our credentials with the help of mysql_config_editor and...
Post a Comment
Read more