My Sql Page/Mariadb Basic Knowledge

Q : how to know Mysql Server is running on your Server .

Ans : Just do telnet  localhost 3306 ; then press enter  , if blank window appear just after it ,means server is running other wise not before doing also check whether telnet is enable on your server or not .

Q : how to connect a mysql server .

 Ans:  Mysql -hlocalhost -uroot -p press enter  .then it will ask for password  ,enter your                    password  ,a prompt like  MySql  > will be shown means you are connected  .

Q : how to create user in Mysql and providing privilege and checking for existing user
     in mysql .
Ans  :
            "  create user 'username'@'localhost'  identified by password 'password' ."
      if you want to give access to user that can access on network or remote user % sign in
      place  of localhost . so command will look as below .

               "create user 'username'@'%'  identified by password 'password' "

   if you want to check whether user is created or not  ,then use mentioned command
                                 "  select user from mysql.user "
    will show you all already created user in mysql database .

  and which privilege is given to user can be check by 
                                   show grants for 'user'@'localhost'

Q : How to Take Backup in Mysql . ?

Ans : mysqldump -u -p  database1,database2  -R -e  --triggers --single-
       transaction  >     C:\FileName.sql   .
      In case of if you want to take backup of all database use -A  in place of  databases ,
      - R is used to include  routines backup in while doing backup 
      -e  for all events  ,  --triggers for triggers  , --single-transaction  without locking the table or          without     interrupting any connection(R/W).
       In case if Mysql dump command is not working , please check installation path of bin is included
       in system Variable of Environment of Windows .

Q: what are ib_logfile0 ,ib_logfile1 and ibdata1 file in MySql.

Ans  : ib_logfile0 ,ib_logfile1 :   are redo log files for innodb table  ,that is used by table for transaction purpose in case to recovery of table from crash , so  suppose you have done any changes in table name as table1 ,so before doing any changes in table first changes will be in innodb_buffer_pool from there it will  write into these two log files ,one by one , in case if one is full , automatically 2nd log will be used by innodb_buffer_pool  . so if table is crashed ,then changes record are stored as dirty log in log ,so if database restart after crash ,it will first check these log files for dirty records ,if it is there then those changes will implemented in table .
Parameter for these log files :

1:   innodb_log_files_in_group  : default value is 2  . 
2:  innodb_log_file_size  : depend upon size available in your disk ,but
                           normally it is not more than 4Gb  and not  more  than size of innodb_buffer_pool_size.
3: innodb_fast_shutdown    : value is 0 ,1 and 2 ,if value is means log file will be                                           purged before shutdown , 1 means log files will  not be purged before shutdown .if value is 2  then  it simulate to crash and crash recovery will be   there while restarting database .
4: innodb_flush_log_at_trx_commit: value is  0,1 and 2  ,if value is 0                               means log files are written and flushed  very second , 1 means log files are                                        written and flush after every transaction , if value is 2 means The value can also be set to 2, which causes the log buffer to be written after every transaction commit and flushes the log files to disk once per second. A value of 2 means that MySQL might think that some changes are written to the log file, but do not persist in the log file after an operating system crash, because the log file was not flushed to disk before a crash.

Ibdata1   :  The file ibdata1 is the system tablespace for the InnoDB infrastructure. It contains several classes for information vital for InnoDB eg table data page , table index pages ,data dictionary ,You can divorce Data and Index Pages from ibdata1 by enabling innodb_file_per_table. This will cause any newly created InnoDB table to store data and index pages in an external .ibd file.


Q : How to start and stop Mysql Service  ?

Ans : For starting use command  net start mysql and for stopping Net stop mysql 

Q: why system error 1067 Occured  in mysql ?

Ans  : if you will do changes in your config data ,or will update my.ini file ,then it might possible you will get  error  1067 system error , in my case i have added one more ibdata file .  so just undo all your changes  . every thing will be fine .

innodb_data_file_path = ibdata1:65757M;ibdata2:50m:autoextend  -- > Wrong 

innodb_data_file_path = ibdata1:100M;ibdata2:50M:autoextend  --> Currect

Q : Selecting User from mysql user ?
Ans : select User from mysql.user ;
Q :how to check  verstion  ?
Ans : select version();

Q: how to change password for a user in mysql ?
Ans : ALTER USER ''@'localhost' IDENTIFIED BY '';
Q : How to know about table detail in MySql  ?
Ans : under database information_schema  ,just explore table tables you will get




+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512) | YES  |     | NULL    |       |
| TABLE_SCHEMA    | varchar(64)  | NO   |     |         |       |
| TABLE_NAME      | varchar(64)  | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)  | NO   |     |         |       |
| ENGINE          | varchar(64)  | YES  |     | NULL    |       |
| VERSION         | bigint(21)   | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)  | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21)   | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21)   | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21)   | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21)   | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21)   | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21)   | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21)   | YES  |     | NULL    |       |
| CREATE_TIME     | datetime     | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime     | YES  |     | NULL    |       |
| CHECK_TIME      | datetime     | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(64)  | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21)   | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255) | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(80)  | NO   |     |         |       |
+-----------------+--------------+------+-----+---------+-------+


Q : how to select table name of a schema .

Ans : normally we can do select database , and then can use "show tables" command to for selecting table ,but

      apart from this we can we can use information_schema.tables table to fetch table  info

      rmation from information_schema .

     Query can be used  " select table_name from information_schema.tables where table_schema = 'schema_name' "

Q: How to set password in Mariadb for first time login ?

Ans: https://www.digitalocean.com/community/tutorials/how-to-reset-your-mysql-or-mariadb-root-password