MySql Database Recovery from Raw Files

---

Disclaimer: This worked under particular case. It may or may not work for everyone.

Scenario:

OS: Ubuntu 12.4 Servedit Edition / x86

MYSQL: Ver 14.14 Distrib 5.5.54, for debian-linux-gnu (i686) using readline 6.2

The OP was running radius for AAA. The disk got faulty for some unknown reasons and were unable to boot from it. There was no database backup [Real example of bad practices] So restoration from mysqldump to new system was not an option there !

Requirements:

We need to restore the Database using mysql raw files. Luckily the faulty disk was able to got attached to other system & we were able to copy the core /var/lib/mysql/ folders (along with all sub folders in it)

---

Quick & Dirty Restoration Step !

Requires some good level of Linux / DB knowledge]

• Setup a test SANDBOX, Install same level of OS along with MYSQL on new system/disk. Create databases / tables as required. Verify all is working by logging to mysql
• Stop the MYSQL service.
• Copy the folder /var/lib/mysql/  [copied from faulty disk] to this new box under /var/lib/mysql/  
• Set the permission on newly copied files/folders
  chown mysql -R /var/lib/mysql/
  

After this point Try to start the MYSQL service , IF it starts successfully & you can see your DATA , then skip below steps , ELSE continue through below steps …

• Edit the /etc/mysql/my.cnf & add following line under [mysqld] section
  innodb_force_recovery = 6
  
• Start MYSQL service & the service will start in Safe Mode with limited working support. Verify if you can able to login to MYSQL service by
  mysql -uroot -pPASS
• If above step works, Export the Database backup using mysqldump cmd e.g:
  mysqldump -uroot -pSQLPASS   radius  >  radius_db_dump_.sql
• Once done, Open the file in nano or any other text editor, & verify if it contains the required data.

Now copy the radius_db_dump_.sql to safe location & you know what to do next 🙂

• Import this mysqldump file to your working radius system !

---

TIPS:

Make sure you have multistage backup strategies in place for any mission critical server.

Example for mysql Database, You can do following

• If your server is VM, then VEEAM B&R will be your best friend & guardian, go for it
• 1st Stage Backup: [Highly recommended for live replication]
  ideally, you should have at least 2 Replica servers & configure either Master-Master or Master-Slave Replication
• 2nd Stage backup:
  Create bash scripts to export DB backup in local folder on a daily basis, (or hourly basis if required]
• 3rd Stage backup:
  Attach external USB disk to the server, and in your backup script, add this usb as additional backup repository
• 4th Stage backup:
  Configure DROPBOX and add it as additional backup repository
• 5th Stage backup:
  The admin should manually copy the backup folders to his desktop so that if all other backups gets failed , this should come in handy.

---

Regard’s
Syed Jahanzaib

 

 

 

Vcenter 6.5: Cannot complete operation due to concurrent modification by another operation

Case#1

We have few ESXI machines managed by Vcenter (all have same 6.5 version). Today when we tried to upgrade compatibility on one vm guest using Vcenter, it gave following error.

Cannot complete operation due to concurrent modification by another operation

After some troubleshooting, it came to my knowledge that there was a pending snapshot made by Veeam B&R software, that was causing the issue. After removal of this snapshot, the compatibility upgraded worked fine, & later we moved this VM from one esxi to another dueto resource strains.

 

Case#2

In one another encounter, whenever we tried to edit the guest VM setting, it gave error “Invalid configuration for device ‘1’.” , for this particular case we simply remove the affected guest VM from the inventory & re-added it and the problem got solve.

Mikrotik with Freeradius/mySQL # Part-22 – Create Dynamic Address List using Mikrotik-Address-List Attribute

---

• FREERADIUS WITH MIKROTIK – Part #1 – General Tip’s>

---

Disclaimer! This is important!

Every Network is different , so one solution cannot be applied to all. Therefore try to understand logic & create your own solution as per your network scenario. Just dont follow copy paste.

If anybody here thinks I am an expert on this stuff, I am NOT certified in anything Mikrotik/Cisco/Linux or Windows. However I have worked with some core networks and I read , research & try stuff all of the time. So I am not speaking/posting about stuff I am formerly trained in, I pretty much go with experience and what I have learned on my own. And , If I don’t know something then I read & learn all about it.

So , please don’t hold me/my-postings to be always 100 percent correct. I make mistakes just like everybody else. However – I do my best, learn from my mistakes and always try to help others.

Regard’s
Syed Jahanzaib~

---

Scenario:

• We have FREERADIUS installed as a AAA system in Ubuntu 16.04 server
• Mikrotik version 6.44 is acting as PPPoE NAS connected with radius for AAA

---

Requirement:

When any user connects with our NAS, he should be added to mikrotik dynamic address list under IP > firewall > address list, so that we can manipulate this address list for different tasks, example mark connections/packets/routing and use them in Queues / Routes section or perform different sort of filtering as required.

In this particular task we are dynamically adding user in particular address list using radius attributes, then using this address list packet marking is being made, and then in Queues we are using these marked packets for different sort of bandwidth policies, example for normal internet we will limit 1mb per user , and for CDN traffic we will add addition 2mb for YT & FB. [and vice versa for different packages accordingly]

 

---

Solution:

We will use Mikrotik-Address-List attribute in radgroupreply section. as shown here.

1# Adding User entry in RADCHECK table so user can authenticate …

mysql> select * from radcheck;
+----+----------+--------------------+----+-------------------+
| id | username | attribute | op | value |
+----+----------+--------------------+----+-------------------+
| 1 | zaib | Cleartext-Password | := | zaib |
+----+----------+--------------------+----+-------------------+
1 rows in set (0.01 sec)

2# Adding Radius Group Reply for 1mb Group, Example 1mb group user will get 1mb dynamic queue plus they will be added dynamically in address list name 1mb

mysql> select * from radgroupreply;
+----+-----------+-----------------------+----+--------------+
| id | groupname | attribute | op | value |
+----+-----------+-----------------------+----+--------------+
| 21 | 1mb | Mikrotik-Rate-Limit | == | 1024k/1024k |
| 22 | 1mb | Mikrotik-Address-List | := | 1mb |
+----+-----------+-----------------------+----+--------------+
2 rows in set (0.00 sec)

2# Adding username ZAIB in the Radius user group & assign him 1mb Group.

 

mysql> select * from radusergroup;
+----+----------+-----------+----------+
| id | username | groupname | priority |
+----+----------+-----------+----------+
| 5 | zaib | 1mb | 1 |
+----+----------+-----------+----------+
1 row in set (0.00 sec)

---

RADTEST:

Now we will test user via RADTEST cmd …

radtest zaib zaib localhost 1812 testing123

Result:

Sending Access-Request of id 130 to 127.0.0.1 port 1812
User-Name = "zaib"
User-Password = "zaib"
NAS-IP-Address = 101.11.11.254
NAS-Port = 1812
Message-Authenticator = 0x00000000000000000000000000000000
rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=130, length=50

Mikrotik-Rate-Limit = "1024k/1024k"
Mikrotik-Address-List = "1mb"

Freeradius Debug Result:

Sending Access-Accept of id 156 to 127.0.0.1 port 34563
Mikrotik-Rate-Limit == "1024k/1024k"
Mikrotik-Address-List := "1mb"
Finished request 32.

Now try to connect user from your user device, & upon connection you will see new address list entry for this user IP ..

& its 1mb queues have been created as well

---

# Mikrotik Mangling & Queueing Section !

Now we will move towards Mikrotik related configuration for mangling & queue. in above steps we added DYNAMIC queue for test purposes, & as we will be using simple queues therefore we need to remove the dynamic queue, Do so , then we will move further …

• Marking upload & download separately for 1mb user address list …

/ip firewall mangle
add action=mark-packet chain=forward comment="1mb users UPLOAD" new-packet-mark=1mb_users_up src-address-list=1mb passthrough=no
add action=mark-packet chain=forward comment="1mb users DOWNLOAD" dst-address-list=1mb new-packet-mark=1mb_users_down passthrough=no

• Creating PCQ base 1mb download/upload limit variable …

/queue type
add kind=pcq name=download-1mb pcq-classifier=dst-address pcq-dst-address6-mask=64 pcq-rate=1024k pcq-src-address6-mask=64
add kind=pcq name=upload-1mb pcq-classifier=src-address pcq-dst-address6-mask=64 pcq-rate=1024k pcq-src-address6-mask=64

• Creating PCQ base simple Queues to actual limit each user with 1mb download/upload …

/queue simple
add name="1mb user DOWN - PCQ" packet-marks=1mb_users_down queue=upload-1mb/download-1mb target=""
add name="1mb user UP - PCQ" packet-marks=1mb_users_up queue=128k-per-user/128k-per-user target=""

 

PC#1

PC#2

---

TIPS:

How to remove all dynamic queues [can be used in script login section]

/queue simple remove [find where dynamic]

---

Conclusion:

As we can see that address list have been created successfully, now we can manipulate it for our different tasks using marked packets for customized PCQ base queues for policy base queueing.

I will write more on it later if manage to get some spare time.

---

 

Regard’s
Syed Jahanzaib