rbachwiki - User contributions [en]

Python Environment Setup

2026-01-05T16:52:10Z

Bacchas: /* Script to import a CSV File into an existing Database */

==MySql Commands My Fav ==

Find and Replace the word "GaGa, Ladyy" in Artists and replace with "Lady GaGa

UPDATE Karaoke set Artists = replace(Artists, "GaGa, Lady", "Lady GaGa");

UPDATE wp2_posts
SET `post_content`= replace(`post_content`, "' rel='sexylightbox[]", "")

Delete

Delete FROM `master2011` WHERE id < 1688

Update single record

UPDATE Karaoke
SET Title = 'Size 10 Red Shoe',
Artists = 'Big Red Shoes
Where DiskId=1234

This will grant all priviliges to the user admin. will only work on local host not remote

GRANT ALL ON *.* TO admin@localhost WITH GRANT OPTION;

FLUSH PRIVILEGES; // this relloads the priviliges

use databasename // this loads the database

Show tables //show tables

Describe tablename//show fields

select * from tablename where Title Like "%fire%" //returns all instance of fire. you can also

select count (*) from tablename// returns number of records

select * from tablename where Diskid="200"; // shows all records with disk id of 200

select diskid, track, title, artists from tablename order by title // display defined columns. sort by title

select artists, count(artists) from Karaoke Group by artists // counts the number of songs by that artists

select artists, group_concat(title order by title separator ' | ' ) from tablename Group by artists // list them songs by artists

select * from passrequest where match (companyname, state) against ("texas") //column has to be fulltext search- fulltext search is when you setup the field proberties for the structure.
<pre>
ALTER TABLE Karaoke AUTO_INCREMENT=15786
Resets the auto_increment, in a situation where some of the most recently added rows were deleted. future insertions will be numbered from 15786 again (unless you still had rows numbered greater than 1234, and then the future insertions will start from the greatest number + 1 ).

DELETE FROM tablename WHERE id=3; // will delete record with id 3
</pre>

==Create mysql database in ubuntu shell==
<pre>
On a default settings, mysql root user do not need a password to authenticate from localhost. In this case, ou can login as root on your mysql server using:

$ mysql -u root

If a password is required, use the extra switch -p:
$ mysql -u root -p
Enter password:

Now that you are logged in, we create a database:
mysql> create database amarokdb;
Query OK, 1 row affected (0.00 sec)

We allow user amarokuser to connect to the server from localhost using the password amarokpasswd:
mysql> grant usage on *.* to amarokuser@localhost identified by 'amarokpasswd';
Query OK, 0 rows affected (0.00 sec)

And finally we grant all privileges on the amarok database to this user:
mysql> grant all privileges on amarokdb.* to amarokuser@localhost ;
Query OK, 0 rows affected (0.00 sec)

And that's it. You can now check that you can connect to the MySQL server using this command:
$ mysql -u amarokuser -p'amarokpasswd' amarokdb
Your MySQL connection id is 12
Server version: 5.0.38-Ubuntu_0ubuntu1-log Ubuntu 7.04 distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
</pre>
== securing MYSQL and setting a Password ==
<pre>
/usr/bin/mysql_secure_installation
</pre>

==Mysql Backup and Restore ==
===List Databases===
'''Login to server '''
mysql

''' List databases'''
show databases;

'''List Tables in a database'''
use databasename
show tables;

===Bakcup a single database===

<pre>
done from bash - no need to log in to mysql
mysqldump -u root -ppassword sugarcrm > sugarcrm.sq

syntax

mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
</pre>
===Backup a single table===
<pre>
mysqldump -u root -ppassword databasename table > sugarcrm.sq
</pre>

===Backup Multiple Database===
* log into mysql

mysql -u root -pyourpassword
mysql> show databases;

'''this will give you a list of databases'''

'''For example, if you want to take backup of both sugarcrm and bugs database, execute the mysqldump as shown below:'''

mysqldump -u root -ptmppassword --databases bugs sugarcrm > bugs_sugarcrm.sql

* Verify the bugs_sugarcrm.sql dumpfile contains both the database backup.

# grep -i "Current database:" /tmp/bugs_sugarcrm.sql

-- Current Database: `mysql`

-- Current Database: `sugarcrm`

=== Backup all the databases:===
<pre>
The following example takes a backup of all the database of the MySQL instance.

# mysqldump -u root -ptmppassword --all-databases > /tmp/all-database.sql

</pre>
=== Backup a specific table:===
<pre>
In this example, we backup only the accounts_contacts table from sugarcrm database.

mysqldump -u root -ptmppassword sugarcrm accounts_contacts > /tmp/sugarcrm_accounts_contacts.sql
</pre>

== <h4 class="yellowbackground">Script to import a CSV File into an existing Database</h4> ==
''' the csv file has to be in that specific directory'''
'''The table has to already exist'''
<pre>
#!/bin/bash
mysql kartest <<EOF
truncate table songs;
load data infile '/var/lib/mysql-files/songs.csv'
into table songs
fields terminated by '|'
enclosed by '"'
lines terminated by '\n'
ignore 1 rows;
EOF

</pre>

== Copy csv file to a dir and run script on server ==
<pre>
#!/bin/bash
fxcopy="/mnt/c/Users/john/Dropbox/Shining Cabinet/My Stuff/sqlitedb"
scp -i ~/.ssh/linode "$fxcopy/filename.csv" 1.1.1.1:/var/lib/mysql-files/
sleep 4
ssh -i ~/.ssh/linode root@1.1.1.1 /var/www/scripts/import.sh

</pre>

==Restore Database==
''' I used this to restore r512 the dumped .sql file in 2025 '''
''' I tried to restore it using phpmyadmin, but it did not work, got many errors. probably because it was a raw sql dump backup'''
* log into mysql
mysql
use databasename
source /path/to/sql/file.sql;

<pre>
mysql -u username -p database < dump_file.sql
</pre>

== Set / Change Mysql Password ==
<pre>
Set / change / reset the MySQL root password on Ubuntu Linux. Enter the following lines in your terminal.

Stop the MySQL Server.
sudo /etc/init.d/mysql stop

Start the mysqld configuration.
sudo mysqld --skip-grant-tables &

Login to MySQL as root.
mysql -u root mysql

Replace YOURNEWPASSWORD with your new password!
UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;
</pre>

== Reset mysql password ==
<pre>
dpkg-reconfigure mysql-server-5.0

or

dpkg-reconfigure mysql-server-5.1

Depending on which package you have installed.

the will bring up the enter password screen for mysql
</pre>

== ==
<pre>

</pre>

== Import csv file into Php Myadmin Database ==
<pre>
Structure has to match exactly or you will get an import error.

"Invalid field count in csv input line 1"

Make sure you count the fields. if there is 6 fileds (including the key) you must have 6 fields to import
</pre>

== Fixing error connecting to database from AppSheet ==
''' The permission has to be fixed to the old version in mysql server '''

ALTER USER 'username'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

----
[[#MySql Commands My Fav|Back To Top]]-[[Main_Page| Home]]
[[Category:Mysql]]

MYSQL COMMANDS

2025-10-06T14:48:00Z

Bacchas: /* Script to import a CSV File into an existing Database */

==MySql Commands My Fav ==

Find and Replace the word "GaGa, Ladyy" in Artists and replace with "Lady GaGa

UPDATE Karaoke set Artists = replace(Artists, "GaGa, Lady", "Lady GaGa");

UPDATE wp2_posts
SET `post_content`= replace(`post_content`, "' rel='sexylightbox[]", "")

Delete

Delete FROM `master2011` WHERE id < 1688

Update single record

UPDATE Karaoke
SET Title = 'Size 10 Red Shoe',
Artists = 'Big Red Shoes
Where DiskId=1234

This will grant all priviliges to the user admin. will only work on local host not remote

GRANT ALL ON *.* TO admin@localhost WITH GRANT OPTION;

FLUSH PRIVILEGES; // this relloads the priviliges

use databasename // this loads the database

Show tables //show tables

Describe tablename//show fields

select * from tablename where Title Like "%fire%" //returns all instance of fire. you can also

select count (*) from tablename// returns number of records

select * from tablename where Diskid="200"; // shows all records with disk id of 200

select diskid, track, title, artists from tablename order by title // display defined columns. sort by title

select artists, count(artists) from Karaoke Group by artists // counts the number of songs by that artists

select artists, group_concat(title order by title separator ' | ' ) from tablename Group by artists // list them songs by artists

select * from passrequest where match (companyname, state) against ("texas") //column has to be fulltext search- fulltext search is when you setup the field proberties for the structure.
<pre>
ALTER TABLE Karaoke AUTO_INCREMENT=15786
Resets the auto_increment, in a situation where some of the most recently added rows were deleted. future insertions will be numbered from 15786 again (unless you still had rows numbered greater than 1234, and then the future insertions will start from the greatest number + 1 ).

DELETE FROM tablename WHERE id=3; // will delete record with id 3
</pre>

==Create mysql database in ubuntu shell==
<pre>
On a default settings, mysql root user do not need a password to authenticate from localhost. In this case, ou can login as root on your mysql server using:

$ mysql -u root

If a password is required, use the extra switch -p:
$ mysql -u root -p
Enter password:

Now that you are logged in, we create a database:
mysql> create database amarokdb;
Query OK, 1 row affected (0.00 sec)

We allow user amarokuser to connect to the server from localhost using the password amarokpasswd:
mysql> grant usage on *.* to amarokuser@localhost identified by 'amarokpasswd';
Query OK, 0 rows affected (0.00 sec)

And finally we grant all privileges on the amarok database to this user:
mysql> grant all privileges on amarokdb.* to amarokuser@localhost ;
Query OK, 0 rows affected (0.00 sec)

And that's it. You can now check that you can connect to the MySQL server using this command:
$ mysql -u amarokuser -p'amarokpasswd' amarokdb
Your MySQL connection id is 12
Server version: 5.0.38-Ubuntu_0ubuntu1-log Ubuntu 7.04 distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
</pre>
== securing MYSQL and setting a Password ==
<pre>
/usr/bin/mysql_secure_installation
</pre>

==Mysql Backup and Restore ==
===List Databases===
'''Login to server '''
mysql

''' List databases'''
show databases;

'''List Tables in a database'''
use databasename
show tables;

===Bakcup a single database===

<pre>
done from bash - no need to log in to mysql
mysqldump -u root -ppassword sugarcrm > sugarcrm.sq

syntax

mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
</pre>
===Backup a single table===
<pre>
mysqldump -u root -ppassword databasename table > sugarcrm.sq
</pre>

===Backup Multiple Database===
* log into mysql

mysql -u root -pyourpassword
mysql> show databases;

'''this will give you a list of databases'''

'''For example, if you want to take backup of both sugarcrm and bugs database, execute the mysqldump as shown below:'''

mysqldump -u root -ptmppassword --databases bugs sugarcrm > bugs_sugarcrm.sql

* Verify the bugs_sugarcrm.sql dumpfile contains both the database backup.

# grep -i "Current database:" /tmp/bugs_sugarcrm.sql

-- Current Database: `mysql`

-- Current Database: `sugarcrm`

=== Backup all the databases:===
<pre>
The following example takes a backup of all the database of the MySQL instance.

# mysqldump -u root -ptmppassword --all-databases > /tmp/all-database.sql

</pre>
=== Backup a specific table:===
<pre>
In this example, we backup only the accounts_contacts table from sugarcrm database.

mysqldump -u root -ptmppassword sugarcrm accounts_contacts > /tmp/sugarcrm_accounts_contacts.sql
</pre>

== <h1 class="yellowbackground">Script to import a CSV File into an existing Database</h1> ==
''' the csv file has to be in that specific directory'''
'''The table has to already exist'''
<pre>
#!/bin/bash
mysql kartest <<EOF
truncate table songs;
load data infile '/var/lib/mysql-files/songs.csv'
into table songs
fields terminated by '|'
enclosed by '"'
lines terminated by '\n'
ignore 1 rows;
EOF

</pre>

== Copy csv file to a dir and run script on server ==
<pre>
#!/bin/bash
fxcopy="/mnt/c/Users/john/Dropbox/Shining Cabinet/My Stuff/sqlitedb"
scp -i ~/.ssh/linode "$fxcopy/filename.csv" 1.1.1.1:/var/lib/mysql-files/
sleep 4
ssh -i ~/.ssh/linode root@1.1.1.1 /var/www/scripts/import.sh

</pre>

==Restore Database==
''' I used this to restore r512 the dumped .sql file in 2025 '''
''' I tried to restore it using phpmyadmin, but it did not work, got many errors. probably because it was a raw sql dump backup'''
* log into mysql
mysql
use databasename
source /path/to/sql/file.sql;

<pre>
mysql -u username -p database < dump_file.sql
</pre>

== Set / Change Mysql Password ==
<pre>
Set / change / reset the MySQL root password on Ubuntu Linux. Enter the following lines in your terminal.

Stop the MySQL Server.
sudo /etc/init.d/mysql stop

Start the mysqld configuration.
sudo mysqld --skip-grant-tables &

Login to MySQL as root.
mysql -u root mysql

Replace YOURNEWPASSWORD with your new password!
UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;
</pre>

== Reset mysql password ==
<pre>
dpkg-reconfigure mysql-server-5.0

or

dpkg-reconfigure mysql-server-5.1

Depending on which package you have installed.

the will bring up the enter password screen for mysql
</pre>

== ==
<pre>

</pre>

== Import csv file into Php Myadmin Database ==
<pre>
Structure has to match exactly or you will get an import error.

"Invalid field count in csv input line 1"

Make sure you count the fields. if there is 6 fileds (including the key) you must have 6 fields to import
</pre>

== Fixing error connecting to database from AppSheet ==
''' The permission has to be fixed to the old version in mysql server '''

ALTER USER 'username'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

----
[[#MySql Commands My Fav|Back To Top]]-[[Main_Page| Home]]
[[Category:Mysql]]