Mysql Best way import and export data using mysql command line in Linux.

Your provided commands demonstrate how to perform a MySQL database dump and import. Here's a breakdown of each command:

1. *Database Dump:*

bash

mysqldump -u DBUSERNAME -p DBPASSWORD DATABSENAME --single-transaction --quick | ionice -c2 -n 7 tee > test.sql

Explanation:

- `mysqldump`: This command is used to create a backup of a MySQL database.

- `-u DBUSERNAME`: Specifies the username to connect to the MySQL server.

- `-p DBPASSWORD`: Prompts for the password of the MySQL user specified by the `-u` option.

- `DATABSENAME`: Specifies the name of the database to dump.

- `--single-transaction`: Ensures that the entire dump is made in a single transaction, ensuring data consistency.

- `--quick`: This option is used for quicker dumping of the data by retrieving rows from the server a row at a time rather than retrieving the entire result set at once.

- `| ionice -c2 -n 7 tee > test.sql`: Pipes the output of `mysqldump` to `ionice` and `tee`. `ionice` is used to set the I/O priority of the process, and `tee` is used to redirect the output to both the terminal and a file named `test.sql`.

2. *Database Import:*

bash

ionice -c 2 -n 7 mysql -u DBUSERNAME -p DATABSENAME < /home/jaydip/Downloads/database/test.sql

Explanation:

- `ionice -c 2 -n 7`: Sets the I/O priority of the subsequent command.

- `mysql`: This command is used to import SQL files into a MySQL database.

- `-u DBUSERNAME`: Specifies the username to connect to the MySQL server.

- `-p DATABSENAME`: Prompts for the password of the MySQL user specified by the `-u` option.

- `< /home/jaydip/Downloads/database/test.sql`: Redirects the contents of the `test.sql` file to the `mysql` command, which then executes the SQL commands to import the data into the specified database.

These commands should effectively create a dump of your MySQL database into the `test.sql` file and then import that dump back into the database specified by `DATABSENAME`. Make sure to replace `DBUSERNAME`, `DBPASSWORD`, and `DATABSENAME` with the appropriate values for your MySQL setup.

mysql database upload using command steps

Hello

I have given Simple step for how to upload database using mysql command


Step 1 CMD command : sudo mysql --host hostname --port 3306 -u databaseUsername -p
step 2 pass : of you system then enter  your db pass
step 3 : after login in mysql hit command : use databases;
step 4  : use your database
step 5 : mysql> source /home/jaydip/Downloads/sql.sql ( Path your database )
and then click on entered button

exmaple

Step 1 CMD command : sudo mysql --host localhost --port 3306 -u root -p
step 2 enter your password : test123
step 3 :  use database
step 4 : mysql> source /home/jaydip/Downloads/sql.sql ( Path your database )
and then click on entered button

convert column name into lowercase in mysql and php

Helllo

convert column name into lowercase in mysql and php

some time we want all table column name in lower case then we can change to manually but here
example you can paste this query after run query. you can change you column name in to lower case


convert column name into lowercase in mysql and php example start.

<?php
$c1 = mysql_connect("localhost","root","");// Connection




$db1 = mysql_select_db("INFORMATION_SCHEMA");



$get_column = mysql_query("SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='data_base_name' AND `TABLE_NAME`='table_name'");

while($row = mysql_fetch_assoc($get_column)){


$old_name = $row['COLUMN_NAME'];
$new_name = strtolower($row['COLUMN_NAME']);
$datatype= $row['DATA_TYPE'];
$size = $row['CHARACTER_MAXIMUM_LENGTH'];



if($row['DATA_TYPE'] !="varchar" && $row['DATA_TYPE'] !="text"){
$query =  "ALTER TABLE mstusers CHANGE $old_name $new_name $datatype".";<br/>";
}else{

$query =  "ALTER TABLE mstusers CHANGE $old_name $new_name $datatype ($size)".";<br/>";
}
echo $query;


}

// Query paste in your  phpmyadmin



?>

Please setup below code replace table_name with you table name and database_name replace with you
database name. also setup up your database connection.

After check all query for convert column name into lowercase in mysql and php.

i think it work for you.

convert column name into lowercase in mysql and php

Truncate all tables in a MySQL database in one command

Truncate all tables in a MySQL database in one command

SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') 
FROM INFORMATION_SCHEMA.TABLES where  table_schema in ('databasename1','databasename2');

if Cannot delete or update a parent row: a foreign key constraint fails

That happens if there are tables with foreign keys references to the table you are trying to drop/truncate.
Before truncating tables All you need to do is:

SET FOREIGN_KEY_CHECKS=0;

Truncate your tables and change it  back to 

SET FOREIGN_KEY_CHECKS=1; 

user this php code
<?php


mysql_connect("localhost","root",'');

mysql_select_db("restaurant");

$truncate = mysql_query("SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') as tables_query FROM INFORMATION_SCHEMA.TABLES where table_schema in ('restaurant')");


while($truncateRow=mysql_fetch_assoc($truncate)){

mysql_query($truncateRow['tables_query']);


}


?>

How to Compare two strings using stored procedure in Mysql

 Compare two strings using stored procedure in Mysql

1. Only need one (1) equals sign to evaluate

 DECLARE @tempo VARCHAR(20)
    SET @tempo = 'test'

IF @tempo = 'test'
  SELECT 'yes'
ELSE
  SELECT 'no'

return yes of this output

DECLARE @tempo VARCHAR(20)
    SET @tempo = 'test1'

IF @tempo = 'test'
  SELECT 'yes'
ELSE
  SELECT 'no'

return no in this output


you can also use interger  as value

set below type code for integer


 SET @temp = 1

IF @temp = 1
  SELECT 'yes'
ELSE
  SELECT 'no'
 
  return yes
 
 


IF @temp = 2
  SELECT 'yes'
ELSE
  SELECT 'no'
 
  return no

Split String with comma Loop in Mysql store Proceture Example

Hello

Here Very Usefull for Split String with comma Loop in Mysql store Proceture Example

Split String with comma Loop in Mysql store proceture
this fully example how to use

Loop In Mysql

and split string in mysql

begin
  
   
   
    SET @InitCounter := 1;
      SET @Param := "11,22,33,44,";
    
 
  
  
      
    myLoop: loop 
   
 SET @NextCounter := LOCATE(',',@Param);

 SET @SUBSTR := SUBSTRING(@Param,@InitCounter,@NextCounter);

 SET @Param := REPLACE(@Param,@SUBSTR,'');

 SET @SUBSTR:= REPLACE(@SUBSTR,',','');

 SET @ParamLength := LENGTH (@Param);


 SELECT @SUBSTR;




                      
        if  
       
             @ParamLength = 0
        then
            leave myLoop;             
        end if;
       
    end loop myLoop;                   
   
END

how to remove index and unique constraint in mysql

Hello

here Example of remove how to remove index and unique constraint in mysql

first check table how many constraint  in table after you can remove.

here the query for show index in table

SHOW INDEX FROM Your-table-Name

after remove all index name display in column

here the query

ALTER TABLE Your-table-Name DROP INDEX Your-field-unique-constraint -name;

Please check this 

Connect to MySQL with PHP in XAMPP / Create a new database

hello this video display conction with mysql in php here display how to connect in xampp. nice video